Forum Discussion

DavidNunes7's avatar
DavidNunes7
Icon for Helper I rankHelper I
4 years ago
Solved

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 ...
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    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.

Pragati11's avatar
Pragati11
Icon for Super User rankSuper User
4 years ago

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's avatar
DavidNunes7
Icon for Helper I rankHelper I
4 years ago

 

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