Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!
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
hi @lyonslat
Create below measure
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.
User | Count |
---|---|
25 | |
10 | |
7 | |
6 | |
6 |
User | Count |
---|---|
30 | |
11 | |
11 | |
9 | |
7 |