How to use distinctcount for multiple columns - DAX

[DavidNunes7]

Hello everyone, I need help with Dax syntax and measurements.
I currently have a table with four columns of data, I need a distinct count between the 4 columns
The table is created by dax, i can't even get the columns in the power query 


Thanks 🙂

[v-jingzhang · Accepted solution]

Hi @DavidNunes7 

You can try this measure

Measure =
COUNTROWS (
    DISTINCT (
        UNION (
            DISTINCT ( 'Table'[Date1] ),
            DISTINCT ( 'Table'[Date2] ),
            DISTINCT ( 'Table'[Date3] ),
            DISTINCT ( 'Table'[Date4] )
        )
    )
)

Note that this also counts the blank value. You could substract 1 from it if you don't want to count blank. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Syndicate_Admin]

I look for the same thing but avoid counting the blank cells.

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[DavidNunes7]

Thanks for your help . It's working perfect.

[DavidNunes7]

Thanks for the answer Just what I want to say is that I would like a different distinction considering as 4 columns.
Some like:

DistintCountCol1 =
DISTINCTCOUNT ( yourTablname[yourColumnName] )AND  ( yourTablname[yourColumnName2] )AND   ( yourTablname[yourColumnName3] )

did you understand?

[DavidNunes7]

Thank you for your help
Basically I need the sum of the distinct lines
I have 4 columns with dates. I need to count the total of different days,  considering all these columns

[Pragati11]

Hi @DavidNunes7 ,

If you are targeting to get a single metric in the end which counts distinct values in each of your columns ans adds them up, then you can write the following measure:

Aggregated Distinct Count =
DISTINCTCOUNT ( yourTablname[Column 1] ) +
DISTINCTCOUNT ( yourTablname[Column 2] ) +
DISTINCTCOUNT ( yourTablname[Column 3] ) +
DISTINCTCOUNT ( yourTablname[Column 4] )

[DavidNunes7]

I tried this way. However the columns can have the same dates. If I do the distinct count by column and then add up, the number of days will be greater, as it will count repeated days. Maybe some way to aggregate the columns and then count...

[Pragati11]

Hi @DavidNunes7 ,

When you want distinctCount in a single measure then what it should be?

• Should it be sum of all distinct counts?
• Should be a concatenated text value with distinct counts delimited by a delimiter?

I am not sure what you are really trying to achieve here.

[Pragati11]

Hi @DavidNunes7 ,

If you created the above table using DAX then you can't see it in Power Query Editor as it shows only the data that is loaded to Power BI along with any transformations done within the same area.

Anything that is calculated using DAX is something done on top of the Power Query area.

To get the distinct count of the column values, you can simply write a DAX measure. For example for one of your columns:

DistintCountCol1 =
DISTINCTCOUNT ( yourTablname[yourColumnName] )

Replace yourTablename and yourColumnName with relevant names in your case.