cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Anonymous
Not applicable

## Distinct Count of Two Columns

Hi guys

How can I calculate a distinct count of two columns?

Measure:= DISTINCTCOUNT( [EmpID1] ) + DISTINCTCOUNT( [EmpID2] ) won't work because EmpID "123" could be present in both EmpID1 and EmpID2, i.e. it will double count it.

I'm looking for something like: Measure:= DISTINCTCOUNT( UNION( VALUES( [EmpID1] ), VALUES( [EmpID2] ) ) ).

Suggestions?

Thanks,

Simon

1 ACCEPTED SOLUTION
Microsoft

Hi Simon_Nuss,

Currently I don’t think only using measure could achieve this.

In addition to concatenate those two columns, we could create a special column to mark the same value in those two columns.

Then use the two distinct value to minus the sum of the same value column, take use of your example here:

Create the calculated column with the following formula:

Samevalue = if(

LOOKUPVALUE( Table1[Gold], Table1[Gold], Table1[Sliver] ) <> BLANK(),

1,

0)

Modify the count measure with the following:

Measure := DISTINCTCOUNT(Table1[Gold])+DISTINCTCOUNT(Table1[Sliver])-calculate(DISTINCTCOUNT(Table1[Sliver]), Table1[Samevalue]=1)

See the result:

Regards

6 REPLIES 6
Community Champion

@Anonymous What you can do is create calculated column that concatenate values from two columns and then get distinct count from that calculated column.

Anonymous
Not applicable

@ankitpatira Thanks for the quick response Ankitpatira.  That's close but not quite what I'm looking for.  Also, my data set is 1.1 billion rows - I cannot afford a calculated column.

Take the below example:

 Gold Medals Silver Medals USA Australia USA Canada Germany Spain Greece USA

The distinct count of Gold Medals is 3, Silver medals is 4.  However, I am looking for the distinct count of the population which in this case would be 6.  This can be done in SQL by performing a distinct count of the union of [Gold Medals] and [Silver Medals].  I'm chasing a DAX solution.

Thanks,

Simon

Microsoft

Hi Simon_Nuss,

Currently I don’t think only using measure could achieve this.

In addition to concatenate those two columns, we could create a special column to mark the same value in those two columns.

Then use the two distinct value to minus the sum of the same value column, take use of your example here:

Create the calculated column with the following formula:

Samevalue = if(

LOOKUPVALUE( Table1[Gold], Table1[Gold], Table1[Sliver] ) <> BLANK(),

1,

0)

Modify the count measure with the following:

Measure := DISTINCTCOUNT(Table1[Gold])+DISTINCTCOUNT(Table1[Sliver])-calculate(DISTINCTCOUNT(Table1[Sliver]), Table1[Samevalue]=1)

See the result:

Regards

Anonymous
Not applicable

@Michel well - I'm seeing Marco Russo this evening so I know what I'll be asking him.  Thanks for your help mate!

Microsoft

Hi Simon_Nuss,

Do you need any further assistance on this topic?

Regards

Anonymous
Not applicable

No thanks, I actually forgot to ask Marco - oh well!  I'll revisit it in a few weeks and try to think of an elegant solution.

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors