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

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)