Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
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?
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!
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
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
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.
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |