DISTINCT COUNT WITH CONDITION AS OF MATRIX TABLE

[ThuyTran]

Hello everyone, 

I have an issue to perform distinct count in this Dim_Attribute table:

Index	review_score	Attribute	Value
1	79.20%	S6	100.00%
1	79.20%	I1	100.00%
1	79.20%	F3	100.00%
1	79.20%	F1	100.00%
1	79.20%	I5	100.00%
1	79.20%	S7	100.00%
1	79.20%	G1	100.00%
1	79.20%	T2	66.00%
1	79.20%	S3	66.00%
1	79.20%	S5	66.00%
1	79.20%	S2	66.00%
1	79.20%	T1	66.00%
1	79.20%	I3	0.00%
2	61.70%	S6	100.00%
2	61.70%	F1	100.00%
2	61.70%	F3	100.00%
2	61.70%	G1	100.00%
2	61.70%	S7	100.00%
2	61.70%	I5	100.00%
2	61.70%	I1	0.00%
2	61.70%	T2	0.00%
2	61.70%	I3	0.00%
2	61.70%	F4	0.00%
2	61.70%	S2	66.00%
2	61.70%	T1	66.00%
2	61.70%	S3	66.00%
2	61.70%	S5	66.00%

I try to distinct count the attribute with value >=75%. When i do the matrix it shows like 

but when I do the count distinct on card it doesnt show correctly when I do this measure:

Green Attribute = CALCULATE (DISTINCTCOUNT(Dim_Attribute[Attribute], FILTER(AVERAGE(Dim_Attribute[Value])>=0.75))

It will count total number of attributes.

Would anyone please suggest me how to look at this matter? Should I do a group by index or how can i perform this?

Thank you very much in advance!

[Anonymous]

Hi @ThuyTran ,

Maybe you can modify your formula like below:

Green Attribute =
CALCULATE (
    DISTINCTCOUNT ( Dim_Attribute[Attribute] ),
    FILTER ( Dim_Attribute, AVERAGE ( Dim_Attribute[Value] ) >= 0.75 )
)

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ThuyTran · Accepted solution]

Thank you, I tried but it did not work so well.

But I figure out sth this morning. I unpivot the attribute colum into multiple columns. Then I put multiple VAR condition for each column >=75% a green and <75% as red.

then return will be sum of count of every attribute.

It's kinda manual but so far it's correct 🙂
much appreciate your help ~~

[Anonymous]

Hi @ThuyTran ,

Thanks for your feedback.

Best Regards,
Adamk Kong

[ExcelMonke]

Hello,

Apologies if I missed something here: what is the expected result?

[ThuyTran]

I want to count the value appearing in the matrix table (which are values >=75%) of the attribute. However, my distinctcount shows 20 (total number of attributes)