Forum Discussion

elsteshepardo's avatar
elsteshepardo
Frequent Visitor
2 years ago

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

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

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      elsteshepardo
      Frequent Visitor
      2 years ago

      Hi Anonymous 

      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

       

       

       

       

       

  • elsteshepardo's avatar
    elsteshepardo
    Frequent Visitor
    2 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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!