Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

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

 

Many thanks in advance!

 

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.

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

Share sample data and expected output

This is basically what the dataset looks like:

 

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

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

 

 

29.01.2020Max Mustermann
29.01.2020Peter Müller
27.01.2020Max Mustermann
24.01.2020Peter Müller
24.01.2020Max Mustermann
23.01.2020Max Mustermann
22.01.2020Max Mustermann
21.01.2020Peter Müller
21.01.2020Max Mustermann
20.01.2020Max 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.

Awesome this worked, many many thanks!!

 

Best regards,

Tim

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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