Forum Discussion
Count Distinct per each column based on unique IDs
Hello, I have the raw data that looks like the one below: UID Quer_1 Query_2 ... Query_40 A Y Y Y N B N Y N Y C Y N N N A Y N Y N D Y Y Y Y A N ...
you could probably do something like this
Total Queries Query 1 = CALCULATE(sumx(values(test[UID]), COUNTROWS(test)), test[Quer_1] ="Y")
however that wouldn't solve the writing a measure per column.
you would probably need to transpose your data in power query and if you plot the query on visual then you could probably create a generic measure
see attached
pivoted is in the format you have now the measure looks like this
test
Total Queries Query 1 = CALCULATE(sumx(values(Pivoted[UID]), COUNTROWS(Pivoted)), Pivoted[Quer_1] ="Y")
more generically would be (unpivoted)
Total Queries for Unpivoted = CALCULATE(SUMX(VALUES('Unpivoted'[UID]), COUNTROWS('Unpivoted')), 'Unpivoted'[Value] = "y")
see attatched
Hello, thank you for your solution. I have unpivoted the data, but the result of the measure is not right. For example for Query 1 the 'A' should be counted only once. Unfortunatelty I cannot remove duplicates in UID as for each query there different Y or N.
--------------
Maybe adding custom column:
Column = [UID] & [Query] &[Value])then something like this:
CALCULATE(COUNTROWS(VALUES(Table[Column])), 'Table'[Value] = "Y"), what do think about it?
