Forum Discussion

Jim_brower's avatar
Jim_brower
Frequent Visitor
7 years ago
Solved

Dynamic Measures, Dax using Switch for Escalation Rates, CPI

RateLookup2017 = SWITCH(VALUES('Escalation'[Escalation]),"Material 1", LOOKUPVALUE('Escalation Rates'[2017],'Escalation Rates'[Category],"Material 1"),"Material 2",LOOKUPVALUE('Escalation Rates'[201...
Need Help
  • Jim_brower's avatar
    Jim_brower
    7 years ago

    Unfortunatly that didn't work with LOOKUPVALUE() function.......I just went back to IF as a Calculated Column which works fine.

    Constant Amount = 
IF (AND(Data[Category] = "Material 1",Data[Year]=2017),
        LOOKUPVALUE('Escalation Rates'[2017],'Escalation Rates'[Category],"Material 1")*[Amount],
            IF (AND(Data[Category] = "Material 2",Data[Year]=2017),
            LOOKUPVALUE('Escalation Rates'[2017],'Escalation Rates'[Category],"Material 2")*[Amount],
                IF (AND(Data[Category] = "Material 3",Data[Year]=2017),
                LOOKUPVALUE('Escalation Rates'[2017],'Escalation Rates'[Category],"Material 3")*[Amount],
IF (AND(Data[Category] = "Material 1",Data[Year]=2018),
        LOOKUPVALUE('Escalation Rates'[2018],'Escalation Rates'[Category],"Material 1")*[Amount],
            IF (AND(Data[Category] = "Material 2",Data[Year]=2018),
            LOOKUPVALUE('Escalation Rates'[2018],'Escalation Rates'[Category],"Material 2")*[Amount],
                IF (AND(Data[Category] = "Material 3",Data[Year]=2018),
                LOOKUPVALUE('Escalation Rates'[2018],'Escalation Rates'[Category],"Material 3")*[Amount],
IF (AND(Data[Category] = "Material 1",Data[Year]=2019),
        LOOKUPVALUE('Escalation Rates'[2019],'Escalation Rates'[Category],"Material 1")*[Amount],
            IF (AND(Data[Category] = "Material 2",Data[Year]=2019),
            LOOKUPVALUE('Escalation Rates'[2019],'Escalation Rates'[Category],"Material 2")*[Amount],
                IF (AND(Data[Category] = "Material 3",Data[Year]=2019),
                LOOKUPVALUE('Escalation Rates'[2019],'Escalation Rates'[Category],"Material 3")*[Amount],                
               0
        )))))))))



     

Jim_brower's avatar
Jim_brower
Frequent Visitor
7 years ago

Thanks - I updated to values - not sure if that is correct.

parry2k's avatar
parry2k
Super User
7 years ago

Jim_brower  i guess you updated the measure in original post. Did it worked? It should be fine thou.

  • Jim_brower's avatar
    Jim_brower
    Frequent Visitor
    7 years ago

    Unfortunatly that didn't work with LOOKUPVALUE() function.......I just went back to IF as a Calculated Column which works fine.

    Constant Amount = 
IF (AND(Data[Category] = "Material 1",Data[Year]=2017),
        LOOKUPVALUE('Escalation Rates'[2017],'Escalation Rates'[Category],"Material 1")*[Amount],
            IF (AND(Data[Category] = "Material 2",Data[Year]=2017),
            LOOKUPVALUE('Escalation Rates'[2017],'Escalation Rates'[Category],"Material 2")*[Amount],
                IF (AND(Data[Category] = "Material 3",Data[Year]=2017),
                LOOKUPVALUE('Escalation Rates'[2017],'Escalation Rates'[Category],"Material 3")*[Amount],
IF (AND(Data[Category] = "Material 1",Data[Year]=2018),
        LOOKUPVALUE('Escalation Rates'[2018],'Escalation Rates'[Category],"Material 1")*[Amount],
            IF (AND(Data[Category] = "Material 2",Data[Year]=2018),
            LOOKUPVALUE('Escalation Rates'[2018],'Escalation Rates'[Category],"Material 2")*[Amount],
                IF (AND(Data[Category] = "Material 3",Data[Year]=2018),
                LOOKUPVALUE('Escalation Rates'[2018],'Escalation Rates'[Category],"Material 3")*[Amount],
IF (AND(Data[Category] = "Material 1",Data[Year]=2019),
        LOOKUPVALUE('Escalation Rates'[2019],'Escalation Rates'[Category],"Material 1")*[Amount],
            IF (AND(Data[Category] = "Material 2",Data[Year]=2019),
            LOOKUPVALUE('Escalation Rates'[2019],'Escalation Rates'[Category],"Material 2")*[Amount],
                IF (AND(Data[Category] = "Material 3",Data[Year]=2019),
                LOOKUPVALUE('Escalation Rates'[2019],'Escalation Rates'[Category],"Material 3")*[Amount],                
               0
        )))))))))