PowerBI : Count Distinct values in one column based on Distinct Values in another column

Question

i have a data for group no and its set value. When the set value is same for all the batches i dont want those batches to be counted. but if there are more than 1 set values in each batch then the dax query should count it as 1.

My current data is like this

 | group_no  | values |
| ---------- | ---------------------- |
| H110201208 | 600                    |
| H110201208 | 600                    |
| H110201208 | 680                    |
| H101201215 | 665                    |
| H109201210 | 640                    |
| H123201205 | 600                    |
| H125201208 | 610                    |
| H111201212 | 610                    |
| H111201212 | 630                    |

I want my output like this

| Group no   | Grand Total |
| ---------- | ----------- |
| H101201215 | 1           |
| H109201210 | 1           |
| H110201208 | 3           |
| H111201212 | 2           |
| H123201205 | 1           |
| H125201208 | 1           |

i want to create another table like the one above using dax so that i can plot graphs n percentages based on its output i want to do this in powerbi using DAX language.

jprzd jprzd · Accepted Answer · 2021-04-26T21:18:26

TABLE =
GROUPBY (
    Groups, //SourceTable
    Groups[ group_no  ],
    "GrandTotal", COUNTX ( CURRENTGROUP (), DISTINCTCOUNTNOBLANK ( Groups[ values ] ) )
)

PowerBI : Count Distinct values in one column based on Distinct Values in another column

1 Answers