Counting number of values excluding some repeats.

[Matt_P]

Hi  I am hoping someone can point me in the right direction...

A single 'data entry' can have multiple rows due to the different data points collected, consider the table below.

U1	F1	F2	F3	F4	Q1	Q2
abc1	AA1234	Y	S1	R1	2	3
abc1	AA1234	Y	S1	R2	2	3
sse1	BB1235	N	S1	R1	2	2
sse1	BB1235	N	S2	R1	2	2
asdfe2	CC1234	Y	S1	R1	3	2
asdfe2	CC1234	Y	S3	R1	3	2
asdfe2	CC1234	Y	S1	R2	3	2
asdfe2	CC1234	Y	S3	R2	3	2
dde34	AA1234	N	S5	R5	1	1

[U1] identifies the data entry
[F1] Identifies the subject
[F2], [Q1] & [Q2] will have the same value per [U1]/[F1] entry
[F3] and [F4] can have different values per [U1]/[F1] entry

Put another way, green values will not be different per [U1]/[F1] entry but red values can be.

I would like a count of [Q1] & [Q2] values for each [U1]/[F1] entry.

EG:

How many 2s in [Q1] for unique data entry
Answer is 2, 1 from abc1/AA1234 and 1 from sse1/BB1235

How many 2s in [Q2] for unique data entry
Answer is 2, 1 from sse1/BB1235 and 1 from asdfe2/CC1234

I would like a count of F3 and F4 values such that each unique combination of [U1]/[F1]/[F3]-[F4] value is counted.

EG:

How many S1s in [F3] for unique data entry
Answer is 3, 1 from abc1/AA1234, 1 from sse1/BB1235 and 1 from asdfe2/CC1234

How many R2s in [F4] for unique data entry
Answer is 2, 1 from abc1/AA1234 and 1 from asdfe2/CC1234

I have currently solved my counting problem by merging columns and counting unique values eg merging U1 and F3 and having a DistinctCount on the new column.  However, i'm looking for a more elegant solutiuon. I do need to catagorize (unpivot) [Q1] and [Q2] so I can use Q1 and Q2 as a ledgend in a graph but I suspect the extra rows will not impact on the counting solution.

Many thanks.

[amitchandak · Accepted solution]

@Matt_P , You can try measures like these two examples

countx(summarize(table, table[U1], table[F1], Table[Q1]),[Q1])

countx(summarize(filter(table, Table[Q1]=2), table[U1], table[F1], Table[Q1]),[Q1])