Calculate with multiple criteria

[lyonslat]

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_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

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

Thank you!

[Anonymous]

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]

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]

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]

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.

[lyonslat]

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.