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 m...
Need Help
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"))

 

Ahmedx's avatar
Ahmedx
Icon for Super User rankSuper User
3 years ago

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)