cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
DavidNunes7
Helper I
Helper I

How to use distinctcount for multiple columns - DAX

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 
Sem título.png

Thanks 🙂

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

Hi @DavidNunes7 

 

You can try this measure

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

vjingzhang_0-1645778366406.png

 

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.

View solution in original post

10 REPLIES 10
v-jingzhang
Community Support
Community Support

Hi @DavidNunes7 

 

You can try this measure

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

vjingzhang_0-1645778366406.png

 

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.

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

Hi,

Share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thanks for your help . It's working perfect.

 
DavidNunes7
Helper I
Helper I

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?

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

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] )

 

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

 

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...

 

Sem título.png

 

 

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.

 

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Pragati11
Super User
Super User

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.

 

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors