Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I can't figure out why my measure doesn't work properly. I'm using a measure for background conditional formatting of a cell based on certain conditions (I'm comparing values between W0, W1, and W2). If it's the lowest it should be red, highest - green, in the middle - yellow.
This is my DAX code
I'm attaching pbix in case if it's more helpful
Solved! Go to Solution.
@powerbiuser444 Weird, I just fixed it but had to use ALL:
Formatting_Value1 =
VAR ValueW0 = CALCULATE(SUM(Data[Value1]),ALL(Data),Data[date]="W0")
VAR ValueW1 = CALCULATE(SUM(Data[Value1]),ALL(Data),Data[date]="W1")
VAR ValueW2 = CALCULATE(SUM(Data[Value1]),ALL(Data),Data[date]="W2")
RETURN
SWITCH(TRUE(),
ValueW0 < ValueW1 && ValueW0 < ValueW2 && SELECTEDVALUE(Data[date])="W0", "red",
ValueW0 <= ValueW1 && ValueW0 >= ValueW2 && SELECTEDVALUE(Data[date])="W0", "yellow",
ValueW0 >= ValueW1 && ValueW0 <= ValueW2 && SELECTEDVALUE(Data[date])="W0", "yellow",
ValueW0 > ValueW1 && ValueW0 > ValueW2 && SELECTEDVALUE(Data[date])="W0", "green", BLANK())
I fixed myself, it should be in the brackets
@powerbiuser444 Weird, I just fixed it but had to use ALL:
Formatting_Value1 =
VAR ValueW0 = CALCULATE(SUM(Data[Value1]),ALL(Data),Data[date]="W0")
VAR ValueW1 = CALCULATE(SUM(Data[Value1]),ALL(Data),Data[date]="W1")
VAR ValueW2 = CALCULATE(SUM(Data[Value1]),ALL(Data),Data[date]="W2")
RETURN
SWITCH(TRUE(),
ValueW0 < ValueW1 && ValueW0 < ValueW2 && SELECTEDVALUE(Data[date])="W0", "red",
ValueW0 <= ValueW1 && ValueW0 >= ValueW2 && SELECTEDVALUE(Data[date])="W0", "yellow",
ValueW0 >= ValueW1 && ValueW0 <= ValueW2 && SELECTEDVALUE(Data[date])="W0", "yellow",
ValueW0 > ValueW1 && ValueW0 > ValueW2 && SELECTEDVALUE(Data[date])="W0", "green", BLANK())
@powerbiuser444 Couldn't access that link. First thing to try, in your measure try returning ValueW1 for example and return that as a value in your matrix versus background color and see if it shows up correctly in the W0 column. That way at least you will know if the calculation for that variable is correct or not within the filter context provided by the visual.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
26 | |
21 | |
12 | |
8 |
User | Count |
---|---|
75 | |
52 | |
45 | |
15 | |
12 |