Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

PBI Distinct Count with multiple rows and column

Hi All,

How to create measure to count DISTINCT IDs from multiple criteria from specific row and column?
Below are sample dataset and the condition to create measures.

 


Criteria: How many unique ID that flows to the prevstepid and stepid using the below condition.
CountID Measure 1 = Distinctcount ID if prevstepid = "Proc1" and stepid="Verify" and prevstepid="Verify" and stepid="Fail" 
CountID Measure 2 = Distinctcount ID  if prevstepid = "Proc1" and stepid="Verify" and prevstepid="Verify" and stepid="Reg2" and prevstepid="Reg2" and stepid="Test2" 
CountID Measure 3 = Distinctcount ID  if prevstepid = "Proc1" and stepid="Verify" and prevstepid="Verify" and stepid="Proc3" and prevstep="Proc3" and stepid="Pass" 


Any help is very much Appreciate..

 

Thank you.




Need Help

15 Replies

  • rajulshah's avatar
    rajulshah
    Resident Rockstar
    3 years ago

    Anonymous 

     

    You can use the following DAX measure-

    Measure1 =
CALCULATE (
    DISTINCTCOUNT ( Table[id] ),
    FILTER (
        Table,
        Table[prevstepid] = "Proc1"
            && Table[stepid] = "Verify"
            && Table[prevstepid] = "Verify"
            && Table[stepid] = "Fail"
    )
)

     

    Like this, you can create the other 2 measures.

    Please let me know if this didn't help.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Not working using the actual data. I tried one filter (Proc1 and Verify) and its working but if 'm using multiple && condition, no values appear on my matrix and card visualization.

       

      • rajulshah's avatar
        rajulshah
        Resident Rockstar
        3 years ago

        Anonymous 
        Can you share the sample data or just copy-paste the data in excel format here?

  • Ahmedx's avatar
    Ahmedx
    Super User
    3 years ago

    what result do you expect from Count Measure 1 according to the screenshot you posted?

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

       

      The value should be 1, if the condition fullfil.

      DistinctCount ID = CALCULATE (DISTINCTCOUNT ( table[id] ),FILTER (table, table[prevstepid] = "proc1" && table[stepid]="Verify"&& table[prevstepid]= "Verify" && table[stepid]="Fail"))

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Is there any idea to use helper column to achieved this condition?

        If my data are horizontally distributed , this condition is easy to do but my data are vertically distributed.
        the problem with Horizontal i need to manually create column every prevstepid and corresponding stepid and diagnosis column(sample below). Is there any idea if I can do this dynamically like the below screenshot. Thanks.

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Just try on the actual data and i'm getting incorresult values wiith only 4 records satisfy with the condition out 222,000 records. It should be 1450 overall count distributed from different month.

    • Ahmedx's avatar
      Ahmedx
      Super User
      3 years ago

      variant without LEN believed?

      Measure = 
VAR _SearchString =
     "Proc1" & "Verify"&"Verify"&"fail"
    
VAR _Filtertable = 
    DISTINCT( 
            ADDCOLUMNS(
                SELECTCOLUMNS('Table'," ID",'Table'[Id]),
                       "CombinWords", CONCATENATEX(FILTER(ALL('Table'),'Table'[Id]=[ ID]),[prevstepid]&[stepid],"")))
VAR _Result = 
       FILTER(_Filtertable,CONTAINSSTRING([CombinWords],_SearchString))
RETURN
           COUNTROWS(_Result)
  • Ahmedx's avatar
    Ahmedx
    Super User
    3 years ago

     pls try this

    Measure = 
VAR _Filtertable = 
    DISTINCT( 
            ADDCOLUMNS( 
                SELECTCOLUMNS('Table'," ID",'Table'[Id]),
                       "CombinWords", CONCATENATEX(FILTER(ALL('Table'),'Table'[Id]=[ ID]),[prevstepid]&[stepid],"")))
VAR _Result = 
       FILTER(_Filtertable,
       CONTAINSSTRING([CombinWords],"Proc1") && 
       CONTAINSSTRING([CombinWords], "Verify")&&
       CONTAINSSTRING([CombinWords], "fail")
       
       )
RETURN
           COUNTROWS(_Result)
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Thank you Ahmedx . with this solution we group all the values inside the particular  ID using concatenate. how I'm sure that the particular prevstepid="proc1" flow to stepid="Verify" and prevstepid="Verify" flow to "fail". I'm  seeing from the data there are also captured with this scenario  prevstepid="proc1" flow to stepid="Verify" and prevstepid ="Verify" flow to "Pass".

     

    • Ahmedx's avatar
      Ahmedx
      Super User
      3 years ago

      I didn't understand you, was it the right decision?

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        I attached sample screen shot based on the result from the measure created. the count equal to 1 meaning the condition fulfilled while if 0, the condition was not satisfy.