Forum Discussion

shoemaker27's avatar
shoemaker27
Frequent Visitor
3 years ago
Solved

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!
 
COUNT
DAX
Distinct
  • tamerj1's avatar
    tamerj1
    3 years ago

    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] )
    )
)
  • tamerj1's avatar
    tamerj1
    3 years ago

    shoemaker27 

    Please try

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

7 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    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's avatar
    shoemaker27
    Frequent Visitor
    3 years ago

    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