PBI Distinct Count with multiple rows and column

[JWick1969]

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.

[JWick1969]

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]

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

[JWick1969]

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.

[Ahmedx]

pls try this

[JWick1969]

Thank you.  I'll try this code in actual data.

[Ahmedx]

 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)

[JWick1969]

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]

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]

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

[JWick1969]

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"))

[Ahmedx]

pls try this

Measure = 
VAR _SearchString =
     "Proc1" & "Verify"&"Verify"&"fail"
VAR _LenString = LEN(_SearchString)     
VAR _Filtertable = 
    DISTINCT( ADDCOLUMNS(
            ADDCOLUMNS(
                SELECTCOLUMNS('Table'," ",'Table'[Id]),
                       "CombinWords", CONCATENATEX(FILTER(ALL('Table'),'Table'[Id]=[ ]),[prevstepid]&[stepid],"")),"@len", LEN([CombinWords])))
VAR _Result = 
       FILTER(_Filtertable,CONTAINSSTRING([CombinWords],_SearchString)&&[@len]=_LenString)
RETURN
           COUNTROWS(_Result)
--------- or -------
Measure = 
VAR _SearchString =
     "Proc1" & "Verify"&"Verify"&"fail"
VAR _LenString = LEN(_SearchString)     
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)

[JWick1969]

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.

[rajulshah]

@JWick1969 

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.

[JWick1969]

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]

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