Match value in table A with B

[Anonymous]

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, 

[jdbuchanan71 · Accepted solution]

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

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[jdbuchanan71 · Accepted solution]

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

[Anonymous]

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.

[Anonymous]

Hi @jdbuchanan71 ,

I tried to create a column and has it formulated as you suggested: count unique map =[usage measure]. It would sum up the distinct count as 6 like below

My usage measure is: usage = DISTINCTCOUNT(TableA[Maps])

If I do the Count (Distinct) of "Maps" in the Values, it would count correctly like "usage", but I cannot use it as a calculated column. 

Is there a way to create a calculated column that works just like the measure for "usage"? 

Thank you, I hope I explain it well. 

[jdbuchanan71]

@Anonymous 

I don't know what your measure for "usage' looks like.  Can you share your .pbix file?

[Anonymous]

Hi @jdbuchanan71 , my usage measure is simple, this is it: usage = DISTINCTCOUNT(TableA[Maps]). 

I do not know how to share a .pbix file here in the forum. Can you walk me through? 

Much appreciate your quick response,

[jdbuchanan71]

You can just upload the file to OneDrive or DropBox then share the link here.

[Anonymous]

Hi @jdbuchanan71 

This is my .pbix file here

I tried to create a calculated column that works the same as the measure "usage", so that I can use the DAX you suggested for MinValue, but no success so far. 

So ultimately, in TableA I need to create calculated columns to LOOKUP for "Tier Ranking" and "Pricing" from Table B. 

Thank you very much for your help, 

[jdbuchanan71 · Accepted solution]

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

[Anonymous]

thank you very much @jdbuchanan71 !