Forum Discussion
DISTINCT COUNT WITH CONDITION AS OF MATRIX TABLE
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!
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 ~~
5 Replies
Hello,
Apologies if I missed something here: what is the expected result?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)
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 KongIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 ~~
