Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 ? ? ...
Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    7 years ago

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

  • jdbuchanan71's avatar
    jdbuchanan71
    7 years ago

    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's avatar
Anonymous
Not applicable
7 years ago

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's avatar
jdbuchanan71
Icon for Super User rankSuper User
7 years ago

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's avatar
    Anonymous
    Not applicable
    7 years ago

    thank you very much jdbuchanan71 !