DAX conditional on multiple text columns

[Anonymous]

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!

[amitchandak]

@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])

[Anonymous]

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