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



     

parry2k's avatar
parry2k
Super User
7 years ago

Jim_brower this DAX seems need attention:

 

RateLookup2017 = 
SWITCH(TRUE(),
"Material 1", 	LOOKUPVALUE('Escalation Rates'[2017],'Escalation Rates'[Category],"Material 1"),
"Material 2",	LOOKUPVALUE('Escalation Rates'[2017],'Escalation Rates'[Category],"Material 2"),
"Material 3", LOOKUPVALUE('Escalation Rates'[2017],'Escalation Rates'[Category],"Material 3"),1)

Highlighted above should return True/False value whereas you just have static value. 

 

 

Jim_brower's avatar
Jim_brower
Frequent Visitor
7 years ago

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