Distinct Count of a Column in a Table Without Replacing the Values

AveragePotato — Thu, 11 May 2023 15:41:45 GMT

Hi,

So I've been struggling with designing a visual a particular way. This seems very simple to me, but it's not proving so. Please help!

I'm designing a simple, lean report. Everything comes from one table, and all metrics are in a Table visual.

I have a column, let's call it vehicle makes, and I need to get a distinct count of the vehicle makes in the total row of the table.

Here is what my visual looks like:

Vehicle Make Vehicle Model

Toyota Camry

Honda Accord

Nissan Rogue

Chevrolet Silverado

Chevrolet Tahoe

Chevrolet Impala

What I need to do is get a distinct count of the "Vehicle Make" column without changing the values of the Vehicle Make.

When I try to do distinct(count) of this column, this is what I get:

Vehicle Make Vehicle Model

1 Camry

1 Accord

1 Rogue

1 Silverado

0 Tahoe

0 Impala

Total 4

This is not what I need because the values of the "Vehicle Make" column change to 1s and 0s when I choose to discount(count)

My desired result is this:

Vehicle Make Vehicle Model

Toyota Camry

Honda Accord

Nissan Rogue

Chevrolet Silverado

Chevrolet Tahoe

Chevrolet Impala

Total 4

I am very frustrated with this and cannot seem to find a working solution. I have tried using measures with VALUES() to achieve this. I have also tried using IF() expression to achieve this, but since there are multiple values to display, I receive the error "a table of multiple values was supplied where a single value was expected."

Please help! Thank you!

Re: Distinct Count of a Column in a Table Without Replacing the Values

OwenAuger — Fri, 12 May 2023 03:23:09 GMT

Hi AveragePotato

The simplest method I can think of is to write a measure like this:

Vehicle Make or Count = IF ( HASONEVALUE ( 'Table'[Vehicle Make] ), -- This condition may need to be refined SELECTEDVALUE ( 'Table'[Vehicle Make] ), DISTINCTCOUNT ( 'Table'[Vehicle Make] ) )

and set the number format appropriately, e.g.

"Total "0;;"Total "0

This measure will then return a variant type, either text or an integer.

Placed in a table visual it would look like this:

Note: The condition for whether to display the Vehicle Make value or the DISTINCTCOUNT may need to be refined, but this should work in general.

Does this work for you?

(PBIX attached)

Regards

topic Re: Distinct Count of a Column in a Table Without Replacing the Values in DAX Commands and Tips

Distinct Count of a Column in a Table Without Replacing the Values

Re: Distinct Count of a Column in a Table Without Replacing the Values