How to use max function to show a text value

[Jishnu]

Hi Team,

I have few data, in that i want to get the latest Date Status of a Task for particular time.

When i'm using 
If(Date=Max(Date) and Hour='14:00',[T4 -> QVD Status])
not getting the Status

Sample data is, (attached)

Could you please some one me.

Thanks,
Jishnu G S

[Anil_Babu_Samineni · Accepted solution]

Perhaps this? Hope you are trying this as calculated dimension?

If(Date=Aggr(Max(Date),Date) and Hour='14:00',[T4 -> QVD Status])

[QFabian]

Hi, please try something like this :

Aux:
LOAD
"Date",
Task,
"Hour" / 24 as "Hour",
[T4 -> QVD Status] as Status
FROM [lib://desktop (fabian_fabian)/1eb7ed41-85b3-4426-966e-ae167f6d13f0.xlsx]
(ooxml, embedded labels, table is Sheet1);

left join
MaxDate:
Load
Task,
max(Date) as MaxDate
Resident Aux
group by
Task;

Result:
Load
*,
If(Date=MaxDate and Hour = 0.583333333, Status) as [T4 -> QVD Status]
Resident Aux;
drop table Aux;

[Jishnu]

Hi QFabian,

Thanks for your response.

I'm looking to use this as a calculated dimension.

[Jishnu]

Hi Anil,

Thanks!

I have modified the script and got the result.

Thanks,
Jishnu G S