Forum Discussion

KiranPatil's avatar
KiranPatil
Frequent Visitor
7 years ago
Solved

DAX formula Help

  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  ...
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

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

  • KiranPatil's avatar
    KiranPatil
    Frequent Visitor
    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
      Microsoft Employee
      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