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 counted, the second row does not get counted, the third and fourth rows get counted. 

 

Please note that the actual table has many other columns and hence no rows are completely unique. 

 

Asset NameCountryOther Columns…
Asset1UK...
Asset1UK...
Asset1Germany...
Asset2Germany...

 

Thanks,

Ali 

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

6 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    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
    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

  • TomMartens's avatar
    TomMartens
    Super 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
      Helper 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 

  • mstew55's avatar
    mstew55
    Frequent Visitor
    1 year ago

    How can i count distinct values, that are dipulicates by rows, based on another column. Example. I have column with Names, that can repeat, and i have column with numbers that can be blank by row. I want to Count the names that has all blanks...No row has a number

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      1 year ago

      Hi,

      Try this measure

      Measure = calculate(distinctcount(Data[Name]),data[Number]=Blank())

      Hope this helps.