Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Need help with DAX to compare rows

Hi Community,

My dataset has data quality issues.

Following is the sample dataset & the result set I want to achieve with a DAX measure. 

Logic: 1. For a client, if date is the same in multiple rows & case_id exists in one of the rows, then count the row with the smallest referrence_id and the row with case_id (here the decision should be the same example client 21783 )

2. for a client, if date is the same in multiple rows but no case_id exists, then count the row with the smallest referrence_id (client 17209 & 21358)

3. for a client if only one row exists, then count that row (clients 17207 ,17456, 14679)

 

 

Pbi file:  https://1drv.ms/u/s!Ag919_pO_UKrgRpl_4ptxVDYbpkJ?e=YySHnU

 

Thanks in advance

Need Help
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    Anonymous Thank you for explaining, I understand it now. Please try below column formula. I return 1 to flag the rows you want. If you want to return Ref_id value, you can use [Ref_id] to replace 1 in if function.

    column = 
var _1 = minx(filter('Table', [Client id] = earlier([Client id]) && [Date] = earlier([Date]) && not(isblank([Case_id]))), [Ref_id])
var _2 = minx(filter('Table', [Client id] = earlier([Client id]) && [Date] = earlier([Date])), [Ref_id])
return
if([Ref_id]=_1 || [Ref_id]=_2, 1, BLANK())

     

    Jing

7 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , Try a new column like this to create a flag

     


    var _1 = minx(filter(Table, [client_id] = earlier([client_id]) && not(isblank([Case ID]))),[Ref Id])
    var _2 = minx(filter(Table, [client_id] = earlier([client_id]) ),[Ref Id])
    return
    if(isblank(_1), _2,_1)

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks amitchandak , but it doesn't work for the scenarios with following clients 21358, 21783 & 22034

       

       

      • v-jingzhang's avatar
        v-jingzhang
        Icon for Community Support rankCommunity Support
        5 years ago

        Hi Anonymous 

         

        You can add [Date]=earlier([Date]) to Amit's formula, just like below.  

        Column = 
var _1 = minx(filter('Table', [Client id] = earlier([Client id]) && [Date] = earlier([Date]) && not(isblank([Case_id]))), [Ref_id])
var _2 = minx(filter('Table', [Client id] = earlier([Client id]) && [Date] = earlier([Date])), [Ref_id])
return
if(isblank(_1), _2, _1)

         

        Regards,
        Community Support Team _ Jing
        If this post helps, please Accept it as Solution to help other members find it.