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 even get the columns in the power query 


Thanks 🙂

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.

10 Replies

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    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 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.

     

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