Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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'[2017],'Escalation Rates'[Category],"Material 2"),"Material 3",LOOKUPVALUE('Escalation Rates'[2017],'Escalation Rates'[Category],"Material 3"),1)

Hi Community
I am relatively new to DAX - and hoping to get some help on this calculation (before I go back to SQL 🙂 ) I am trying to create a dynamic calculation for Nomincal vs Constant dollars based on multiple rates.

I have 2 main formulas (but is not applying the way I was hoping)

1. Step 1 - Find Rates by Category and Year
`RateLookup2017 = SWITCH(VALUES('Escalation'[Escalation]),"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)`
2. Step 2 - Apply Rates based on Selection of Nominal vs Constant
```2017 Dynamic = IF(HASONEFILTER('Escalation'[Escalation]),
SWITCH(
VALUES('Escalation'[Escalation]),
"Constant",
CALCULATE(Sum(Data[Amount]),Data[Year] IN {2017} ),
"Nominal",
[RateLookup2017]*CALCULATE(SUM(Data[Amount]),Data[Year] IN {2017} )
,BLANK()),Blank())```

MdxScript(Model) (14, 238) Calculation error in measure 'Measures 1'[RateLookup2017]: Function 'SWITCH' does not support comparing values of type True/False with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.

OS Version:

Thanks in Advance for any help ( I can email PBIX)
1 ACCEPTED SOLUTION
Frequent Visitor

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

5 REPLIES 5
Super User

@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.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Frequent Visitor

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

Super User

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

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Frequent Visitor

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

Super User

here is another post using switch with tru.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors