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

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

  • 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 !