Count of distinct values of a column grouped by another column

[Anonymous]

Hi

Is it possible to write a DAX formula to count distinct values of a column grouped by another column please?

For example, in the below table, the Count column displays the distinct count of 'Pricing' grouped by D_Reference, L_Number combination. All I'm trying to do is to write a DAX formula to calculate the values in the Count column as displayed below.

Any help would be super appreciated. Thanks in advance.

D_Reference	L_Number	Pricing	Count
D1	1	A	2
D1	2	B	2
D1	3	A	2
D2	1	A	1
D2	2	A	1
D2	3	A	1
D3	1	A	3
D3	2	B	3
D3	4	C	3

[v-shex-msft]

HI @Anonymous ,

You can write a measure formula to calculate distinct count of current group:

Measure =
CALCULATE (
    COUNTROWS ( VALUE ( Table[Pricing] ) ),
    VALUES ( Table[D_Reference] ),
    VALUES ( Table[L_Number])
)

BTW, you expected result seems only filter by 'D_Reference' field, 'L_Number' not use in calculation.

Regards,
Xiaoxin Sheng

[Anonymous]

Apologies @v-shex-msft . Here's the list of revised count values that I'm trying to calculate (As you can see the count values now pertain to distinct count of Pricing values grouped by D_Reference and L_Number)

I tried to use the formula that you provided however, I see a syntax error message as below..

Do you have any advice?

[AnkitBI · Accepted solution]

Give below a try

Measure = countrows(filter(all('Table'),'Table'[D_Reference] = max('Table'[D_Reference]) && 'Table'[L_Number] = max('Table'[L_Number])))

Thanks
Ankit Jain

Do Mark it as solution if the response resolved your problem. Do like the response if it seems good and helpful.

[Anonymous]

How do i write this query if want to group only by one column

[Anonymous]

Thanks @AnkitBI . This seems to work as I expect. Although, I don't quite understand the formula. For instance, would you mind explaining the point of having the max functions in the formula?

'Table'[D_Reference] = max('Table'[D_Reference]) && 'Table'[L_Number] = max('Table'[L_Number])

Thanks again. 

[AnkitBI]

Max is to get the current values of fields from the visual.

[AnkitBI]

How your count is grouped by line number. It seems only grouped by d_reference