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