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

[AveragePotato]

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!

[OwenAuger]

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