topic Re: Using variable result in another Variable? in DAX Commands and Tips

Using variable result in another Variable?

ptmuldoon — Thu, 19 Sep 2024 14:29:05 GMT

I am trying to use the result of 1 variable into a 2nd variable in computing a value. I know I can do this with nested Switch Statements, but trying to see if this methos would work as well.

In the below, I am trying to replace [QTY] value in the Var MyItem and in the Return dependent upon what is selected from the Var ItemValue. Yet, it returns an incorrect value when I try it.

Can anyone maybe help troubleshoot the below? Is it even possible?

Thank you,

Paul

Occupied Rooms = VAR ItemValue = SWITCH ( VALUES ('Measures List'[Fact Period]), "Day", [QTY], "Last 7", [Last 7 QTY], "MTD", [MTD QTY], "QTD", [QTD QTY], "YTD", [YTD QTY] ) Var Myitem = CALCULATE([QTY],'Dim Item'[description]="OCCUPIED ROOMS") RETURN SWITCH ( VALUES ('FactType'[FactType]), "#", Myitem, "% of Avail. Rooms", DIVIDE( Myitem, CALCULATE([QTY],'Dim Item'[description]="AVAILABLE ROOMS") ) )

Re: Using variable result in another Variable?

DataInsights — Thu, 19 Sep 2024 15:45:45 GMT

ptmuldoon,

Field parameters offer a robust way to capture the selected measure. You could create a field parameter using the five measures in variable ItemValue.

https://learn.microsoft.com/en-us/power-bi/create-reports/power-bi-field-parameters

Re: Using variable result in another Variable?

dharmendars007 — Thu, 19 Sep 2024 16:40:23 GMT

Hello ptmuldoon ,

You can try the below code..

Occupied Rooms =
VAR ItemValue =
SWITCH (
VALUES ('Measures List'[Fact Period]),
"Day", [QTY],
"Last 7", [Last 7 QTY],
"MTD", [MTD QTY],
"QTD", [QTD QTY],
"YTD", [YTD QTY])

VAR MyItem =
CALCULATE(ItemValue, 'Dim Item'[description] = "OCCUPIED ROOMS")

RETURN
SWITCH (
VALUES ('FactType'[FactType]),"#", MyItem,"% of Avail. Rooms", DIVIDE( MyItem, CALCULATE([QTY], 'Dim Item'[description] = "AVAILABLE ROOMS") ))

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes are much appreciated!

Thank You

Dharmendar S

Re: Using variable result in another Variable?

ptmuldoon — Thu, 19 Sep 2024 19:50:26 GMT

I'm definitely struggling with this today, and I think the issue may have something to do with the ability to pass a variable meaure into the Calculate function?

I tried with the field paramater, and also tried @dharmendars007 code, and get the same incorrect value.

If I place the Switch statement directly within the Calculate function, it works as expected. But if change out the Switch statement with variable name in the Calculate, it returns an incorrect value?

Can you use a variable measure in Calculate?

EDIT. Pretty sure you can't use the Variable in the Calculate, and finally found an explanation here that makes sense. The Variable gets totaled before the filtering in the Calculate, thus the wrong response is given.

https://radacad.com/caution-when-using-variables-in-dax-and-power-bi

Occupied Rooms = VAR _Period = SWITCH ( VALUES ('Measures List'[Fact Period]), "Day", [QTY], "Last 7", [Last 7 QTY], "MTD", [MTD QTY], "QTD", [QTD QTY], "YTD", [YTD QTY] ) RETURN CALCULATE( SWITCH ( VALUES ('Measures List'[Fact Period]), "Day", [QTY], "Last 7", [Last 7 QTY], "MTD", [MTD QTY], "QTD", [QTD QTY], "YTD", [YTD QTY] ) ,'Dim Item'[description]="OCCUPIED ROOMS" )