Calculate with multiple criteria

Question

Hi All,&nbsp;&nbsp;First post, so apologies if it doesn't make a huge amount of sense, but I'm really hoping someone can help me.&nbsp;&nbsp;Relatively new to DAX, but very familiar with Excel, so most of my logical thinking (&amp; language) comes from there.&nbsp;&nbsp;ProblemI need to count the number of distinct transactions...&nbsp;Transactions = 
DISTINCTCOUNT('Event1'[check_number])&nbsp;&nbsp;...that contain "Food" AND "Beer".&nbsp;&nbsp;The data I have is formatted in the following table:&nbsp;profit_center_idProfit_center_namecheck_numberrevenue_category_nameItemQty15M1 Food123456Food115M1 Food123456Beer215M1 Food123456Misc116M2 Beer234567Food117M3 Beer345678Beer117M3 Beer345678Food2&nbsp;I need more than this, but if I have an answer to this, I can figure the rest out!&nbsp;Thank you!

anonymous · Answer

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

lyonslat · Answer

Hi Pravin,&nbsp;&nbsp;Thanks for your input, it's much appreciated.&nbsp;&nbsp;Unfortunatley this didn't quite work the way I expected it to.&nbsp;&nbsp;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.&nbsp;&nbsp;Any suggestions?&nbsp;Thanks,&nbsp;Ben&nbsp;

lyonslat · Answer

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.

anonymous · Answer

hi&nbsp;lyonslat&nbsp;&nbsp;Create below measure&nbsp;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")returnIF(food&gt;0 &amp;&amp; beer&gt;0,1,0)&nbsp;And add this measure to visual level filter of table and set it to 1.&nbsp;&nbsp;&nbsp;Thanks &amp; regards,Pravin Wattamwarwww.linkedin.com/in/pravin-p-wattamwarIf I resolve your problem Mark it as a solution and give kudos.

hotchilli · Answer

You could use INTERSECT in the followingMeasureFoodAndBeer = 
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.

Forum Discussion

Calculate with multiple criteria

5 Replies

Related content

Sequential Order column based on multiple criteria

Calculate SUM with Multiple Criteria

DAX FILTER with multiple criteria

CALCULATE with multiple OR/AND criteria

DAX calculate with multiple criteria

profit_center_id	Profit_center_name	check_number	revenue_category_name	ItemQty
15	M1 Food	123456	Food	1
15	M1 Food	123456	Beer	2
15	M1 Food	123456	Misc	1
16	M2 Beer	234567	Food	1
17	M3 Beer	345678	Beer	1
17	M3 Beer	345678	Food	2