topic Re: Count Distinct Measure at lowest level in DAX Commands and Tips

Count Distinct Measure at lowest level

shoemaker27 — Tue, 28 Mar 2023 10:47:32 GMT

Hi!, Im struggling with this measure, Im not getting the result I want, I have this table:

sale_id	sale_type	person
1	1	a
2	1	b
3	2	c
4	2	x
5	2	y
6	2	z
7	3	a

I want a measure that calculates the distinct count of person for each sale_type, the result should be like this:

sale_id	sale_type	person	Distinct_Person_Per_Sale_Type
1	1	a	2
2	1	b	2
3	2	c	4
4	2	x	4
5	2	y	4
6	2	z	4
7	3	a	1

This measure works but only If I not add the sale_id to the table:

Distinct_Person_Per_Sale_Type = CALCULATE(

DISTINCTCOUNT('Table'[person]), ALLEXCEPT('Table','Table'[sale_id],'Table'[sale_type])

)

How can I achieve that the measure works at the lowest level of the table (sale_id)?

thanks a lot for your guidance and help!

Re: Count Distinct Measure at lowest level

tamerj1 — Tue, 28 Mar 2023 12:00:16 GMT

Hi shoemaker27
Pleas try

Distinct_Person_Per_Sale_Type = COUNTROWS ( CALCULATETABLE ( VALUES ( 'Table'[person] ), ALL ( 'Table' ), -- can use ALLEXCEPT and include filter safe columns if any VALUES ( 'Table'[sale_type] ) ) )

Re: Count Distinct Measure at lowest level

shoemaker27 — Tue, 28 Mar 2023 12:21:08 GMT

excellent!! one more workaround, the person dimension:

person	kind
a	director
b	manager
c	manager
x	manager
y	director
z	manager

If I put an slicer for "Kind", the count returns the same result, and should be the distinct count of person but for the kind selected, I added "kind" to the allexcept but the numbers dont change!

any workaround for this!

thanks a lot! tamerj1

Re: Count Distinct Measure at lowest level

tamerj1 — Tue, 28 Mar 2023 12:40:22 GMT

shoemaker27
Please share some details about your data model

Re: Count Distinct Measure at lowest level

shoemaker27 — Tue, 28 Mar 2023 14:11:48 GMT

The model is like this:

Re: Count Distinct Measure at lowest level

tamerj1 — Tue, 28 Mar 2023 15:48:38 GMT

shoemaker27

Please try

Distinct_Person_Per_Sale_Type =
COUNTROWS (
CALCULATETABLE (
VALUES ( 'Table'[person] ),
ALLSELECTED ( 'Table' ),
VALUES ( 'Table'[sale_type] )
)
)

Re: Count Distinct Measure at lowest level

shoemaker27 — Wed, 29 Mar 2023 07:12:10 GMT

Awesome!! thanks!!!

Still trying to understand how works the filter VALUES ( 'Table'[sale_type] ) on the context.

Re: Count Distinct Measure at lowest level

tamerj1 — Wed, 29 Mar 2023 07:25:46 GMT

shoemaker27
ALLSELECTED removes all inner filters imposed byu the visual itself but keeps the outer filters imposed by slicers, page filters and visual level filters. VALUES restores the filter of sales_type