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.

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

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?

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

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.