Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
shoemaker27
Frequent Visitor

Count Distinct Measure at lowest level

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

sale_idsale_typeperson
11a
21b
32c
42x
52y
62z
73a

 

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

 

sale_idsale_typepersonDistinct_Person_Per_Sale_Type
11a2
21b2
32c4
42x4
52y4
62z4
73a1

 

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!
 
2 ACCEPTED SOLUTIONS
tamerj1
Super User
Super User

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

View solution in original post

@shoemaker27 

Please try

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

View solution in original post

7 REPLIES 7
shoemaker27
Frequent Visitor

excellent!! one more workaround, the person dimension:

personkind
adirector
bmanager
cmanager
xmanager
ydirector
zmanager

 

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 

 

@shoemaker27 
Please share some details about your data model

The model is like this:

shoemaker27_0-1680012687470.png

 

@shoemaker27 

Please try

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

Awesome!! thanks!!!

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

@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
Super User
Super User

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors