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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
lyonslat
Frequent Visitor

Calculate with multiple criteria

Hi All, 

 

First post, so apologies if it doesn't make a huge amount of sense, but I'm really hoping someone can help me. 

 

Relatively new to DAX, but very familiar with Excel, so most of my logical thinking (& language) comes from there. 

 

Problem

I need to count the number of distinct transactions...

 

Transactions = 
DISTINCTCOUNT('Event1'[check_number])

 

 

...that contain "Food" AND "Beer". 

 

The data I have is formatted in the following table:

 

profit_center_idProfit_center_namecheck_numberrevenue_category_nameItemQty
15M1 Food123456Food1
15M1 Food123456Beer2
15M1 Food123456Misc1
16M2 Beer234567Food1
17M3 Beer345678Beer1
17M3 Beer345678Food2

 

I need more than this, but if I have an answer to this, I can figure the rest out!

 

Thank you!

5 REPLIES 5
Anonymous
Not applicable

Calculate (Distinctcount(table[column]),filter(table,containstring(table[columm_name],"food") || containstring(table[columm_name],"beer")))

Thanks
Pravin

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

Hi Pravin, 

 

Thanks for your input, it's much appreciated. 

 

Unfortunatley this didn't quite work the way I expected it to. 

 

The result I'm looking for is to show the number of transactions that contain both Food and Beer. The result that your suggestion put forward, gives me a number of the transactions that contain either food or beer and totals them up. 

 

Any suggestions?

 

Thanks, 

Ben 

Anonymous
Not applicable

hi @lyonslat 

 

Create below measure

 

Flag =
var food=CALCULATE(COUNT(Sheet1[profit_center_id]),allexcept(Sheet1,Sheet1[profit_center_id]),Sheet1[revenue_category_name]="FOOD")
Var beer=CALCULATE(COUNT(Sheet1[profit_center_id]),allexcept(Sheet1,Sheet1[profit_center_id]),Sheet1[revenue_category_name]="BEER")
return
IF(food>0 && beer>0,1,0)
 
And add this measure to visual level filter of table and set it to 1.
 
 
 
Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar
Capture1.PNG
If I resolve your problem Mark it as a solution and give kudos.

You could use INTERSECT in the following

MeasureFoodAndBeer = 
VAR _FoodChecks = CALCULATETABLE (VALUES ( TableBill[check_number] ), TableBill[revenue_category_name] = "Beer")
VAR _BeerChecks = CALCULATETABLE (VALUES ( TableBill[check_number] ), TableBill[revenue_category_name] = "Food")

RETURN
 COUNTROWS(INTERSECT(_BeerChecks, _FoodChecks))

If you want to break this down and test it, you can materialise each of the tables by creating a table (New Table) with the CALCULATETABLE code. You can then look at the table in data view.

The best way I can describe this is if you imagine a venn diagram of transactions.

 

One side has food transactions, the other has beer, and in the middle you have the number of distinct transactions that contain both food and beer. 

 

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.