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
in https://www.youtube.com/watch?v=zHzSBNES6jE it is explained how to use a measure to color the bars in a bar chart.
this works fine, but i want to take it up to the next level 😊
I want to refer to a 'specs' table for this.
Area | Lower | Upper | Color |
Garden | 0 | 20 | #0000FF |
Garden | 20 | 50 | #00FF00 |
Garden | 50 | 100 | #FF0000 |
Is this possible?
Can anybody help me on the way?
thx
Hans
I just noticed it is not possible to add images in here
Solved! Go to Solution.
to be honest, @V-lianl-msft , I did not try your solution.
Reason: I found a solution myself:
Hi @hanswittoeck ,
Tre this measure:
Measure = SWITCH(TRUE(),
MAX('Table'[Area])="Garden"&&MAX('Table'[Qty])>0&&MAX('Table'[Qty])<=20,"#0000FF",
MAX('Table'[Area])="Garden"&&MAX('Table'[Qty])>20&&MAX('Table'[Qty])<=50,"#00FF00",
MAX('Table'[Area])="Garden"&&MAX('Table'[Qty])>50&&MAX('Table'[Qty])<=100,"#FF0000")
It should be pointed out that when there is a legend field in the bar chart, conditional formatting is not possible.
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
thx @V-lianl-msft , but in my table next to Garden, there will be other entries. Your solution needs to be extended each time new entries are added to the specs table. I shouldd be able to FILTER in one or another way
Hi @hanswittoeck ,
Maybe I misunderstood you. Please try this measure.
Measure = SWITCH(TRUE(),
MAX('Table'[Qty])>0&&MAX('Table'[Qty])<=20,"#0000FF",
MAX('Table'[Qty])>20&&MAX('Table'[Qty])<=50,"#00FF00",
MAX('Table'[Qty])>50&&MAX('Table'[Qty])<=100,"#FF0000")
If the problem persists,could you share the sample pbix via cloud service like onedrive for business?(Please mask any sensitive data before uploading)
It's better to share the results you expected, so that we can know more about the scenario.
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Sorry it took some time for me to answer.
Intention is that the coloring happens automatically based on the values in SPECS.
If the sum of the 'NofOrders' is above lower and below upper the appropriate collor will be applied.
If you select another Functional_loc_L2, the intervals will change.
But it's not clear to me how i can share the pbix with you. I made a stripped version and saved it to the company onedrive but now i am stuck 😕
Hans
Hi @hanswittoeck ,
You may still need hard coding. As you said, the interval for each area is different, so you need to create different filter conditions for each area. Just like the previous formula.
You can share sample pbix through OneDrive links.
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
i was hoping something like would do the trick
Hello All,
I am getting closer to the solution
Hi @hanswittoeck ,
Try
sum_nofoeders= CALCULATE (
SUM ( ORDER_HEADER[nofOrders] ),
ALLEXCEPT(ORDER_HEADER,ORDER_HEADER[Area])
)
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
to be honest, @V-lianl-msft , I did not try your solution.
Reason: I found a solution myself:
@hanswittoeck , I have not tested . Try a measure like this and return you color code colum
Color Date = if(FIRSTNONBLANK('Date'[Date],TODAY()) <today(),"lightgreen","red")
and use that in conditional formatting with the field option
Thank you for your reply @amitchandak .
The coloring as such works, my major problem is refer to the 'specs' table to find the correct value. What i need is in plain language
colorKPI = if specs.area =area of current record
if Qty > specs.Lower and QTY < specs.Upper
then specs.Color
But i cannot 'translate' this into DAX.
Hans
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 |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |