Count Distinct Measure at lowest level

[shoemaker27]

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!

[tamerj1 · Accepted solution]

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] )
    )
)

[shoemaker27]

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 

[tamerj1]

@shoemaker27 
Please share some details about your data model

[shoemaker27]

The model is like this:

[tamerj1 · Accepted solution]

@shoemaker27 

Please try

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

[shoemaker27]

Awesome!! thanks!!!

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

[tamerj1]

@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

[tamerj1 · Accepted solution]

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] )
    )
)