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 | Y | Y | N |
And I need to transfotm the data into:
| Query_1 | Number of Y per each unique ID |
| Query_2 | Number of Y per each unique ID |
| Query_3 | Number of Y per each unique ID |
| ... | Number of Y per each unique ID |
| Query_40 | Number of Y per each unique ID |
I did this by removing duplicates from UID column and then calculating Y, but I noticed that is the wrong order and I have wrong number of Y, as I should only count Y per each Query, but I imagine that it's possible to create a universal measure instead of creating measure per query.
My question is, how to count Y but considering to not duplicate to UID? From VBA perspective I could do it using Collection or Dictionary, but how to achive it using DAX?
4 Replies
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 measuresee attachedpivoted is in the format you have now the measure looks like thistestTotal 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 attatchedHello, 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?
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?
