cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Frequent Visitor

## SUM of all [(Count of combination of values in column 1 & 2) >=1 should give me the value 1]

Hello all,

im fairly new to this programm so this might be the easiest thing and im just too stupid.

I'm looking basically for the in the subject described formula in dax but just cant seem to get it.

SUM of all [(Count of combination of values in column 1 & 2) >=1 should give me the value 1]

But because the Combination of value in column 1 & column 2 could exist multiple times, this needs to be excluded.

Further: in the end I need 1 result that should be giving me the information: the combination of those values exists exactly 5 times.

Best regards

1 ACCEPTED SOLUTION
Anonymous
Not applicable
You just take distinct count of any column...it will return you value 1 for each duplicate combinations...
Just drag above two columns in table visual...n drag the measure...it will return 8 and 10 for above respective datasets.

In case you want to show it it card visual, use below measure.

Measure= sumx(summerize(table,table[column1],table[column2],"total",distinctcount(table[column2])),[total])

Thanks and regards,
Pravin Wattamwar

If it resolves your problem mark it as a solution and give Kudos.
4 REPLIES 4
Anonymous
Not applicable
Share sample data and expected output
Frequent Visitor

This is basically what the dataset looks like:

 29.01.2020 Max Mustermann 29.01.2020 Peter Müller 22.01.2020 Max Mustermann 23.01.2020 Max Mustermann 24.01.2020 Max Mustermann 27.01.2020 Max Mustermann 20.01.2020 Max Mustermann 21.01.2020 Max Mustermann 22.01.2020 Max Mustermann 27.01.2020 Max Mustermann

In this case I would want to have the output 8.

 29.01.2020 Max Mustermann 29.01.2020 Peter Müller 27.01.2020 Max Mustermann 24.01.2020 Peter Müller 24.01.2020 Max Mustermann 23.01.2020 Max Mustermann 22.01.2020 Max Mustermann 21.01.2020 Peter Müller 21.01.2020 Max Mustermann 20.01.2020 Max Mustermann

This table on the other hand should give me the output 10.

The dates wont neccessary come from my source in any specific order. (thats why I once have an order and once not)

Anonymous
Not applicable
You just take distinct count of any column...it will return you value 1 for each duplicate combinations...
Just drag above two columns in table visual...n drag the measure...it will return 8 and 10 for above respective datasets.

In case you want to show it it card visual, use below measure.

Measure= sumx(summerize(table,table[column1],table[column2],"total",distinctcount(table[column2])),[total])

Thanks and regards,
Pravin Wattamwar

If it resolves your problem mark it as a solution and give Kudos.
Frequent Visitor

Awesome this worked, many many thanks!!

Best regards,

Tim

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors