Using a Measure or a Variable in a Measure doesn't work - but the Number does?

[elsteshepardo]

I'm trying to replicate another great Bas power bi visual - it's the status tracker, but it's giving me a problem that I just don't understand.

This is his original code (using my tables)

Dummy line hightlight =
VAR _Selected_Status = SELECTEDVALUE('DS Requests'[Status])
VAR _Selected_Status_Nr =
CALCULATE(MAX('Status'[Order]),'Status'[Status] = _Selected_Status)

VAR _Result =
IF(SELECTEDVALUE('Status'[Order]) <= _Selected_Status_Nr, [Dummy])
RETURN
_Result

This brings back [Dummy] (in this case =  1) but ONLY for the selected DS Requests Status

If I change the code and just enter a number, e.g. 6, rather than the CALCULATE formula

Dummy line hightlight =
VAR _Selected_Status = SELECTEDVALUE('DS Requests'[Status])
VAR _Selected_Status_Nr = 6
//CALCULATE(MAX('Status'[Order]),'Status'[Status] = _Selected_Status)

VAR _Result =
IF(SELECTEDVALUE('Status'[Order]) <= _Selected_Status_Nr, [Dummy])
RETURN
_Result

I get the results I want (i.e. all other status numbers that are less than 6 are given a 1)

Why?

I also tried to create a separate measure just for the Calculate part and then use that instead

SelectedStatusNumber = CALCULATE(MAX('Status'[Order]),'Status'[Status] = SELECTEDVALUE('DS Requests'[Status]))

Dummy line hightlight =
VAR _Selected_Status = SELECTEDVALUE('DS Requests'[Status])
VAR _Result =
IF(SELECTEDVALUE('Status'[Order]) <= [SelectedStatusNumber], [Dummy])
RETURN
_Result

That didn't work either - i.e. I only got a 1 when for the selected status

I also thought, why is this so complicated - why can't the measure just be:

Dummy line hightlight =
IF(SELECTEDVALUE('Status'[Order]) <= [SelectedStatusNumber], [Dummy])

This does not work

But even with this one, if I just add a number (e.g. 6)

Dummy line hightlight =
IF(SELECTEDVALUE('Status'[Order]) <= 6, [Dummy])

I get the results that I want

So, how come a) nothing seems to work other than just entering the actual number, b) why can't the measure just be the IF statement?

Thanks

Steven

[zoewebb]

Hi! I was having the same issues, I was never able to figure out exactly why the DAX code does not work as intended. However, I did find a work around. 

Using transform data I  took the Status Column (Info like "Waiting for approval", "Shipping", etc) and I added a conditional column that esentially converts the status information to the same information as you have in the 'Status'[Order] Column. Because of the nature of my data I had to do a couple other steps. However, I think you should be able to use this new column as your _Selected_Status_Nr possibly with selected value (assuming you are using a slicer like he did in the video?

Dummy Line Highlight2 =

VAR _Selected_Status_Number = insert formula for grabbing number from condition column (possibly using SELECTEDVALUE function)

RETURN

    IF(

        MAX('Status Table'[Index]) <= _Selected_Status_Number,

        [Dummy],

        BLANK()

    )

I am no Power BI expert, but I think what solved the porblem for me was using a table value for this variable rather than a measure value.

Hope this helps!

[elsteshepardo]

Sorry to be a pain - but does anyone know if this is an obvious issue?

Why does the calculation not 'work' as intended when the output is the same

Thanks

[v-yangliu-msft]

Hi  @elsteshepardo ,

Is it possible that getting 'Status'[Status]=maximum value of current DS Requests'[Status]'Status'[Order]) is itself less than or equal to 6, so it all comes up with the same result when it comes to the IF() function

The use of Var() function: you can temporarily save the value of the expression output in the form of a named variable, then you can pass it as a parameter to other metrics, and mesaure display a single value is different, var() can save a single value, you can save a whole virtual table of data, the value displayed is unchanged.

For more details about VAR, you may read the REMARKS:

https://docs.microsoft.com/en-us/dax/var-dax 

[DAX] Why and how to use variables - Microsoft Fabric Community

Variables in DAX - SQLBI

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[elsteshepardo]

Hi @v-yangliu-msft 

Not sure - all I know is that the result I want to achieve only works when I enter a number, rather than use some kind of function

Being consistent with the above post - using 6 in the Measure (which is equal to Approved to next stage) I get this result

But when I use something else, like

VAR _Selected_Status_Nr =
CALCULATE(MAX('Status'[Order]),'Status'[Status] = _Selected_Status)

It doesn't work, I get this

But I know that the calculation is correct, because

CALCULATE(MAX('Status'[Order]),'Status'[Status] = _Selected_Status)

for this example, returns 6