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

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

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

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

        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.