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 ...
I'd start by using the unpivot transform in Power Query to get it in form:
UID, Query Name, Value
Then add a custom column that has 1 if Y and 0 if N
At that point you can use AVERAGEX in a measure to do something like:
AVERAGEX (
VALUES ( Table[UID] ),
CALCULATE (
SUM ( Table[CustomColumn]
)
)
Yoi can then use that measure in a matrix with Query Name in the rows.
That might need a bit of tweaking but I think the principal is sound.
Hello, thank you for your solution, but for Query 1 the 'A' should be counted only once, not twice, how to modify it?
----------
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?
