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.

     

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

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