Forum Discussion
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...
Ahmedx
Super User
Super Userwhat result do you expect from Count Measure 1 according to the screenshot you posted?
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"))
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.- 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)