DAX formula Help

[KiranPatil]

ID	Status Time	Stage	Status
C11	01-04-2018	Screening	Pending
C11	02-04-2018	Screening	Rejected
C11	03-04-2018	Screening	Pending
C11	04-04-2018	Screening	Pending

Hi Guys ,

Need Help on DAX
I have above Transaction , As you can see for Stage "Screening " there are mutiple Status " Pending" for ID "C11".
I need to get Max of Pending Based on Status Time in same table with different column ( like Flag or something ).

Output should be C11 , "04-04-2018","Screening","Pending"
Please help me to retrive this using DAX or any other Method .

Advance Thanks .

[KiranPatil · Accepted solution]

Hi @v-jiascu-msft ,

I have the above transaction and i need to create in calculated column due to obvious reasons . 
For each ID i need Screening Pending MAX Status updated Date .

May be can create one Flag , Please help how to write in the DAX to achieve this .

Output should be

A1 Sreening Pending 04-09-18

A2 Sreening Pending 06-09-18

A3 Sreening Pending 08-09-18

A4  Sreening Pending 09-09-18

Advance thanks 🙂

[v-jiascu-msft]

Hi @KiranPatil,

Could you please mark the proper answers as solutions?

Best Regards,

Dale

[KiranPatil]

Hi @v-jiascu-msft , Yeah will mark it and thanks for the help .

I have one more doubt . Hope you reply to that as well

[v-jiascu-msft]

Hi @KiranPatil,

You can filter the Status and choose the latest Status Time. Please refer to the snapshot below.

Best Regards,

Dale

[KiranPatil]

Hi ,

Need help on this DAX -

Interviewed = if('T1"[Stagename]="Screening" && ('T1'[StatusSummary]="Selected" || 'rec_ana trn_Applicant_Trn'[StatusSummary]="Rejected" || 'rec_ana trn_Applicant_Trn'[StatusSummary]="Dropped") && ('rec_ana trn_Applicant_Trn'[Company_code]="C001" || 'rec_ana trn_Applicant_Trn'[Company_code]="C004"),DISTINCTCOUNT('rec_ana trn_Applicant_Trn'[id]))

- When i write this DAX formula it is not grouping by ID   . Example - as for 1 particular ID will have 2 Statussummary " Selected and " Rejected " , It is taking count as 2 for that particular ID but i need count as 1 .

Example - for the DAX i have written above , iam getting count as 7 but i need count as 4 .

For 1st ID it has 2 transaction , Stage="Screening" and Status="Selected " and "Rejected" so it is taking 2 count.

Please help

[v-jiascu-msft]

Hi @KiranPatil,

I would suggest you create a measure instead of a calculated column. Can you share a complete sample? Seems there is another table.

Best Regards,

Dale

[KiranPatil · Accepted solution]

Hi @v-jiascu-msft ,

I have the above transaction and i need to create in calculated column due to obvious reasons . 
For each ID i need Screening Pending MAX Status updated Date .

May be can create one Flag , Please help how to write in the DAX to achieve this .

Output should be

A1 Sreening Pending 04-09-18

A2 Sreening Pending 06-09-18

A3 Sreening Pending 08-09-18

A4  Sreening Pending 09-09-18

Advance thanks 🙂

[v-jiascu-msft · Accepted solution]

Hi @KiranPatil,

The formula of Flag could be like below. Please check the demo in the attachment.

Flag =
VAR maxDate =
    CALCULATE (
        MAX ( Table1[Status Updated Date] ),
        FILTER (
            'Table1',
            'Table1'[id] = EARLIER ( Table1[id] )
                && Table1[Stage] = "Screening"
                && Table1[Status] = "Pending"
        )
    )
RETURN
    IF ( [Status Updated Date] = maxDate, 1, 0 )

Best Regards,

Dale