cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## distinct count of multiple columns

My data contains FName, LName, MName, Gender, Card ID, Health ID, Active Flag and there may be Null in any column for each row i am trying to calculate distinct count  (FName+Card ID+Health ID) and distinct count  (FName+Card ID+Health ID+Where Gender=M)

 FNAME LNAME MNAME Gender Card ID Health ID ActiveFlag AB CD X M 1234567 12345 Y BC EF Y M 9810561 67891 N AB CD X M 1234567 12345 Y IH PQ Z M 4567890 34564 N IM PY C F 56789 N F 1456478 67890 N ME EE B M 78901 N AB CD X M 1234567 12345 Y BC EF Y M 9810561 67891 N

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Solution Sage

## Re: distinct count of multiple columns

Create two measures and try the following Dax

`Measure1 = COUNTROWS(GROUPBY(User,User[FNAME],User[Card ID],User[Health ID],User[Gender]))`
`Measure2 = COUNTROWS(FILTER(GROUPBY(User,User[FNAME],User[Card ID],User[Health ID],User[Gender]),User[Gender]="M"))`

4 REPLIES 4
Highlighted
Resident Rockstar

## Re: distinct count of multiple columns

Hi

1. Create a new column :   New_Col = 'Table1'[FNAME]&'Table1'[Card ID]&'Table1'[Health ID]

2. Then you can create measure to do the count as needed:

Dist_cnt = CALCULATE(DISTINCTCOUNT('Table1'[New_Col]))

Dist_Male_Cnt=CALCULATE(DISTINCTCOUNT('Table1'[New_Col]),'Table1'[Gender]="M")

Hope this helps.

Thanks
Raj

Highlighted
Solution Sage

## Re: distinct count of multiple columns

Create two measures and try the following Dax

`Measure1 = COUNTROWS(GROUPBY(User,User[FNAME],User[Card ID],User[Health ID],User[Gender]))`
`Measure2 = COUNTROWS(FILTER(GROUPBY(User,User[FNAME],User[Card ID],User[Health ID],User[Gender]),User[Gender]="M"))`

Highlighted
Frequent Visitor

## Re: distinct count of multiple columns

Thanks Jessica the Measures are working.

Highlighted
Resolver II

## Re: distinct count of multiple columns

Hi,

I am wondering which is the best solution in terms of performance?

Concatenate Calculated Column ?
OR
GroupBy Measure ?

Rob

Announcements

#### Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

#### Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021