Forum Discussion
DAX conditional on multiple text columns
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!
3 Replies
- amitchandak
Super User
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.
- amitchandak
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
