How to remove two duplicate columns and do filter

[tyan]

I would like to count unique value of  COLOR and MODEL (same color& same model happen muti time count once)

and filter only count the amount of color&model that is CANCEL ( those I highlight in yellow for example) 

Below table is the result I would like to see in powerbi matrix:

[v-kkf-msft · Accepted solution]

Hi @tyan ,

You can try the following two methods.
1. Remove duplicate rows in the Power Query Editor and create the following measure.

        

Count of Cancel = 
CALCULATE (
    DISTINCTCOUNT ( Table1[Model] ),
    Table1[Analysis] = "Cancel"
)

2. Use the following measure directly.

Measure = 
CALCULATE (
    DISTINCTCOUNT ( Table2[Model] ),
    FILTER (
        DISTINCT ( Table2 ), 
        Table2[Analysis] = "Cancel"
    )
)

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[Anonymous]

Calculate(DISTINCTCOUNT('TABLE',[Color]),
        Filter('TABLE', [Analysis] = 'Cancel'