Forum Discussion

aloosh89's avatar
aloosh89
Icon for Helper I rankHelper 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
Icon for Super User rankSuper User
3 years ago

Hey aloosh89 ,

you can create a calculated column using DAX like so:

# of distinct Assets = 
CALCULATE(
    DISTINCTCOUNT( 'Table'[Asset Name] )
    , ALLEXCEPT( 'Table' , 'Table'[Country] )
)

The table will look like this:

It's required to identify the first row inside a group (defined by Country) if you want to suppress the calculation for subsequent rows in the group.

If you want to use a measure, than this can provide you are looking for:

# of distinct Assets (ms) = 
CALCULATE(
    DISTINCTCOUNT( 'Table'[Asset Name] )
    , ALLSELECTED( 'Table'[Asset Name] )
)

A table visual using the measure:


I hope this gets you started and helps you tackle your challenge.

Regards,

Tom

  • aloosh89's avatar
    aloosh89
    Icon for Helper I rankHelper I
    3 years ago

    Hi TomMartens ,

     

    Thanks for providing this solution. What I was hoping for is that the caluclation just returns the count. As in for the example I provided it would return 3. Can you please point me how to do that?

     

    Thanks,

    Ali