Forum Discussion

KiranPatil's avatar
KiranPatil
Frequent Visitor
8 years ago
Solved

DAX formula Help

 

IDStatus TimeStageStatus
C1101-04-2018ScreeningPending
C1102-04-2018ScreeningRejected
C1103-04-2018ScreeningPending
C1104-04-2018ScreeningPending

 

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 .

General Comment
  • KiranPatil's avatar
    KiranPatil
    7 years ago

    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's avatar
    v-jiascu-msft
    7 years ago

    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 )

    formula_help

     

    Best Regards,

    Dale

7 Replies

    • KiranPatil's avatar
      KiranPatil
      Frequent Visitor
      7 years ago

      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's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi KiranPatil,

     

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

    DAX_formula_Help

     

     

    Best Regards,

    Dale

    • KiranPatil's avatar
      KiranPatil
      Frequent Visitor
      7 years ago

      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's avatar
        v-jiascu-msft
        Microsoft Employee
        7 years ago

        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