Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hi,
I am having issues with conditional formatting within the Azure map visual for filled color. Essentially, the table I have records shipments and whether they were on-time, early, delayed. This data is by day, by state. I have set-up a conditional column that records the appropriate hex code for the assignments above. However, the conditional formatting option by field only allows for a FIRST or LAST situation. This leads to a misleading map at a state level as some states are showing Red but really should be green because he majority of their shipments were early.
Example: WV is showing red in an aggregated view but only 1 out of 20 shipments were late
Is there a way that I can format by state on an aggregated basis? Gradient color fomatting doesnt give me the result that I want,
My other idea was to create a consolidate table with just state and calcs by state but not sure how that would work with a day time slicer on the page.
Any suggestions would be greatly appreciated.
Hi @Anonymous ,
We suggest you create a measure to show the most shipment status per state:
Most Frequent Shipment Status =
VAR EarlyCount = CALCULATE(COUNTROWS('Table'), 'Table'[Shipment Status] = "Early", ALLEXCEPT('Table', 'Table'[State]))
VAR LateCount = CALCULATE(COUNTROWS('Table'), 'Table'[Shipment Status] = "Late", ALLEXCEPT('Table', 'Table'[State]))
VAR OnTimeCount = CALCULATE(COUNTROWS('Table'), 'Table'[Shipment Status] = "On-time", ALLEXCEPT('Table', 'Table'[State]))
RETURN
SWITCH(
TRUE(),
EarlyCount > LateCount && EarlyCount > OnTimeCount, "Early",
LateCount > EarlyCount && LateCount > OnTimeCount, "Late",
OnTimeCount > EarlyCount && OnTimeCount > LateCount, "On-time",
"Early"
)
Then use the measure to format the color as shown below:
Best regards,
Joyce
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous I tried this but it still isnt filtering properly... master_sku is linked to a table w/ brand category and the relationship is set to both
MostFrequestShipmentStatus =
VAR earlycount = CALCULATE(COUNTROWS(fbm_delayed_shipements),fbm_delayed_shipements[status] = "Early", ALLEXCEPT(fbm_delayed_shipements,fbm_delayed_shipements[status],fbm_delayed_shipements[master_sku],fbm_delayed_shipements[location_id]))
VAR Latecount = CALCULATE(COUNTROWS(fbm_delayed_shipements),fbm_delayed_shipements[status] = "Late", ALLEXCEPT(fbm_delayed_shipements,fbm_delayed_shipements[status],fbm_delayed_shipements[master_sku],fbm_delayed_shipements[location_id]))
VAR ontimecount = CALCULATE(COUNTROWS(fbm_delayed_shipements),fbm_delayed_shipements[status] = "On time", ALLEXCEPT(fbm_delayed_shipements,fbm_delayed_shipements[status],fbm_delayed_shipements[master_sku],fbm_delayed_shipements[location_id]))
RETURN
SWITCH(
TRUE(),
earlycount > Latecount && earlycount > ontimecount, "Early",
Latecount > earlycount && Latecount > ontimecount, "Late",
ontimecount > earlycount && ontimecount > Latecount, "On-time",
"Early")
Hi @Anonymous ,
ALLEXCEPT will ignore dimensional table slicer, remove it to check the result.
Most Frequent Shipment Status =
VAR EarlyCount = CALCULATE(COUNTROWS('Table'), 'Table'[Shipment Status] = "Early")
VAR LateCount = CALCULATE(COUNTROWS('Table'), 'Table'[Shipment Status] = "Late")
VAR OnTimeCount = CALCULATE(COUNTROWS('Table'), 'Table'[Shipment Status] = "On-time")
RETURN
SWITCH(
TRUE(),
EarlyCount > LateCount && EarlyCount > OnTimeCount, "Early",
LateCount > EarlyCount && LateCount > OnTimeCount, "Late",
OnTimeCount > EarlyCount && OnTimeCount > LateCount, "On-time",
"Early"
)
Best regards,
Joyce
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Joyce!
Thanks for the response. I removed the allexcept formula and it doesnt seem to be working as expected, still.
Most_Freq_Ship_Status =
VAR earlycount = CALCULATE(COUNTROWS(fbm_delayed_shipements),fbm_delayed_shipements[status] = "Early")
VAR latecount = CALCULATE(COUNTROWS(fbm_delayed_shipements),fbm_delayed_shipements[status] = "Late")
VAR ontimecount = CALCULATE(COUNTROWS(fbm_delayed_shipements),fbm_delayed_shipements[status] = "On Time")
RETURN
SWITCH(
TRUE(),
earlycount>latecount && earlycount > ontimecount, "Early",
latecount > earlycount && latecount > ontimecount, "Late",
ontimecount > earlycount && ontimecount > latecount, "On-time",
"Early"
)
Any additional thoughts? Thanks!
Hi @Anonymous !
Thanks for responding! This works for the highest level of data. However, when I filter by a dimensional table tied to this table, the visual is showing green for a state that only has a late order. Do I need to add something to the measure that accounts for each slicer on the page?
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
76 | |
74 | |
57 | |
39 | |
33 |
User | Count |
---|---|
71 | |
66 | |
57 | |
49 | |
47 |