Getting a distinct count based on another column

[aloosh89]

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 Name	Country	Other Columns…
Asset1	UK	...
Asset1	UK	...
Asset1	Germany	...
Asset2	Germany	...

Thanks,

Ali 

[Ashish_Mathur · Accepted solution]

Hi,

Try these measures

AC = DISTINCTCOUNT(Data[Asset Name])

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

Hope this helps.

[mstew55]

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]

Hi,

Try this measure

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

Hope this helps.

[TomMartens · Accepted solution]

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

[Ashish_Mathur · Accepted solution]

Hi,

Try these measures

AC = DISTINCTCOUNT(Data[Asset Name])

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

Hope this helps.

[TomMartens]

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]

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