Forum Discussion

lyonslat's avatar
lyonslat
Frequent Visitor
6 years ago

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 log...
Anonymous's avatar
Anonymous
Not applicable
6 years ago

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

If I resolve your problem Mark it as a solution and give kudos.
HotChilli's avatar
HotChilli
Icon for Community Champion rankCommunity Champion
6 years ago

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.