Forum Discussion
Match value in table A with B
Hi all,
I am trying to write a measure to match the usage in table A with the pricing in table B.
Table A:
| customer_name | Type | usage | Tier ranking | pricing |
| A | Standard | 20 | ? | ? |
| B | Standard | 55 | ? | ? |
| C | Executive | 55 | ? | ? |
| D | Executive | 126 | ? | ? |
Table B:
| Type | Tier ranking | Min | Max | pricing |
| Standard | 1 | 0 | 5 | $0.00 |
| Standard | 2 | 6 | 15 | $50.00 |
| Standard | 3 | 16 | 35 | $48.00 |
| Standard | 4 | 36 | 70 | $45.88 |
| Standard | 5 | 71 | 125 | $43.63 |
| Standard | 6 | 126 | 205 | $41.25 |
| Executive | 1 | 0 | 5 | $100.00 |
| Executive | 2 | 6 | 15 | $95.00 |
| Executive | 3 | 16 | 35 | $90.00 |
| Executive | 4 | 36 | 70 | $85.00 |
| Executive | 5 | 71 | 125 | $80.00 |
| Executive | 6 | 126 | 205 | $75.00 |
The DAX measure I need to calculate is the "Tier Ranking" and "Pricing" in table A; it would match the usage accordingly to the Min and Max in table B to decide which tier a customer is in, and how much they should be charged.
So the result should be like this:
| customer_name | Type | usage | Tier ranking | pricing |
| A | Standard | 20 | 3 | $48.00 |
| B | Standard | 55 | 4 | $45.88 |
| C | Executive | 55 | 4 | $85.00 |
| D | Executive | 126 | 6 | $75.00 |
I have been trying to use a lot of IF functions but it's not working. I greatly appreciate any advice and help!
Thank you all,
Hello Anonymous
If the Min values from table B are static then you can add a calculated column to get the Min target for Table A based on the usage.
MinValue = VAR RowUsage = TableA[usage] RETURN SWITCH ( TRUE (), RowUsage < 6, 0, RowUsage < 16, 6, RowUsage < 36, 16, RowUsage < 71, 36, RowUsage < 126, 71, 126)Then it is just a couple of LOOKUPVALUE columns to get Tier and Pricing
Tier Ranking = LOOKUPVALUE ( TableB[Tier ranking], TableB[Type], TableA[Type], TableB[Min], TableA[MinValue] )
Pricing = LOOKUPVALUE ( TableB[pricing], TableB[Type], TableA[Type], TableB[Min], TableA[MinValue] )
Hello Anonymous ,
We can't easily create a calculated column on table A becasue the usage is a distinct count of table A. This measure will pull the pricing based on the usage and tier though.
Pricing = VAR Tier = SELECTEDVALUE ( TableA[Tier Type] ) RETURN CALCULATE( MAX(TableB[pricing]), FILTER( TableB, TableB[Type] = tier && [usage] >= TableB[Min] && [usage] <= TableB[Max] ) )
11 Replies
- jdbuchanan71
Super User
Hello Anonymous
If the Min values from table B are static then you can add a calculated column to get the Min target for Table A based on the usage.
MinValue = VAR RowUsage = TableA[usage] RETURN SWITCH ( TRUE (), RowUsage < 6, 0, RowUsage < 16, 6, RowUsage < 36, 16, RowUsage < 71, 36, RowUsage < 126, 71, 126)Then it is just a couple of LOOKUPVALUE columns to get Tier and Pricing
Tier Ranking = LOOKUPVALUE ( TableB[Tier ranking], TableB[Type], TableA[Type], TableB[Min], TableA[MinValue] )
Pricing = LOOKUPVALUE ( TableB[pricing], TableB[Type], TableA[Type], TableB[Min], TableA[MinValue] )
Hi jdbuchanan71 , thank you very much for your solution. It works perfectly for this sample model. However, when I apply to my actual model, where the "usage in table A is a measure (distinct count of a text), the calculated column MinValue does not work. It returns 0 for all. I am not sure how to work around this issue. Thank you very much for your help.
- jdbuchanan71
Anonymous
Try adding the usage as a calculated column in Table A simply by adding a column and putting in =[usage measure]. Does that get you what you need? If not, please share your .pbix file so we can see how the data is actually organized.
- Ashish_Mathur
