Count Distinct per each column based on unique IDs

[maxon]

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?

[vanessafvg]

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

[maxon]

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?

[bcdobbs]

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.

[maxon]

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?