Forum Discussion

aloosh89's avatar
aloosh89
Helper I
3 years ago
Solved

Getting a distinct count based on another column

  Hello,   I would like to do a distinct count of 'Asset Name'. But when the country is different, I want to be able to count the Asset name again. For example in the below, the first row gets cou...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    3 years ago

    Hi,

    Try these measures

    AC = DISTINCTCOUNT(Data[Asset Name])
    Measure = SUMX(VALUES(Data[Country]),[AC])

    Hope this helps.

  • TomMartens's avatar
    TomMartens
    3 years ago

    Hey aloosh89 ,

     

    you can use this single measure (I prefer a single measure approach):

    Measure = 
SUMX(
    VALUES( 'Table'[Country] )
    , CALCULATE(
        DISTINCTCOUNT( 'Table'[Asset Name] )
        , ALLEXCEPT('Table' , 'Table'[Country] )
    )
)

    The measure can be used inside.a table and also on a Card visual. The measure creates the value of 3 in the Total of a Table visual and also on a Card visual, but also in a single line of the table visual, I added values to the Other column to simulate your requirement - "the actual table has many other columns and hence no rows are completely unique":

    Hopefully, this provides what you are looking for.

    Regards,
    Tom

TomMartens's avatar
TomMartens
Super User
3 years ago

Hey aloosh89 ,

 

you can use this single measure (I prefer a single measure approach):

Measure = 
SUMX(
    VALUES( 'Table'[Country] )
    , CALCULATE(
        DISTINCTCOUNT( 'Table'[Asset Name] )
        , ALLEXCEPT('Table' , 'Table'[Country] )
    )
)

The measure can be used inside.a table and also on a Card visual. The measure creates the value of 3 in the Total of a Table visual and also on a Card visual, but also in a single line of the table visual, I added values to the Other column to simulate your requirement - "the actual table has many other columns and hence no rows are completely unique":

Hopefully, this provides what you are looking for.

Regards,
Tom