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,
I am looking to create a measure that is conditional on two attributes.
Unfortunately I am not able to come up with the DAX expression that allows me to reference Customer[Group] or Sales[Category].
It relates to two different data tables below.
Customer table:
Group | Name | Rate |
A | John | 50% |
B | Fred | 50% |
C | Alex | 100% |
Sales table:
Name | Category | Amount |
John | Food | 100 |
John | Beverage | 200 |
Fred | Food | 50 |
Fred | Beverage | 50 |
Fred | Equipment | 100 |
Alex | Food | 200 |
Alex | Beverage | 50 |
Alex | Equipment | 100 |
I was thinking I would need to use SUMX, but also thought it would work like below;
New Rate = IF(Customer[Group]="B" && Sales[Category]="Equipment", 100% , else Customer[Rate])
New Amount= sum(Sales[Amount]) * [New Rate]
End result should be;
Name | Category | New Amount |
John | Food | 50 |
John | Beverage | 100 |
Fred | Food | 25 |
Fred | Beverage | 25 |
Fred | Equipment | 100 |
Alex | Food | 200 |
Alex | Beverage | 50 |
Alex | Eqiupment | 100 |
and it needs to work on an aggregate basis too;
Name | New Measure |
John | 150 |
Fred | 150 |
Alex | 350 |
Thanks in advance!
@Anonymous , Try like
New Rate = IF(maxx(filter(Customer, Customer[name] =sales[name]),Customer[Group])="B" && Sales[Category]="Equipment", 100% , else Customer[Rate])
or
New Rate = IF(related(Customer[Group])="B" && Sales[Category]="Equipment", 100% , else Customer[Rate])
Thanks @amitchandak ,
The first option almost works, but I cannot reference sales[name] for the filter part, as it is in a different table I believe.
Unfortunately the second option is exactly what I had in mind, but I cannot reference either Group or Category. It only lets me reference measures.
@Anonymous , first one should work as new column in sales, Are you trying as measure.
refer my video - https://www.youtube.com/watch?v=czNHt7UXIe8
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
25 | |
10 | |
7 | |
6 | |
6 |
User | Count |
---|---|
30 | |
11 | |
11 | |
10 | |
6 |