Make a clustered bar chart with conditional formatted leged in Deneb.

[jaryszek]

Hello,

I am trying to use Deneb as my chart in ordet to replace clustered bar chart with legend colors. 

For bar chart without legend I am using Dax Measure like this:

Color by Rank = 
VAR r = [Rank (Current Axis)]
RETURN
SWITCH (
    TRUE(),
    ISBLANK ( r ) || r > 5, "#B3B3B3",
    r = 1, "#E81123",
    r = 2, "#107C10",
    r = 3, "#0078D4",
    r = 4, "#FFB900",
    r = 5, "#8E8CD8"
)

Power bi limitation is that you can not use measure and condtional format it:

How to make this in Deneb? 
Anyone did this? 

My bar chart is here:

https://drive.google.com/file/d/14lpEWK-MQ6JMU5_15WVh8Gv22AiGvdpR/view?usp=sharing

Will be extremely grateful for help,
Best,
Jacek

[v-ssriganesh]

Hello @jaryszek ,

We hope you're doing well. Could you please confirm whether your issue has been resolved or if you're still facing challenges? Your update will be valuable to the community and may assist others with similar concerns.

Thank you.

[jaryszek]

Hello, nope code is not working for me. 

[v-ssriganesh]

Hello @jaryszek,

Could you please confirm if you’ve tried the suggestion from my earlier reply?

If the issue still persists, kindly share:

• A screenshot of your Deneb field wells.
• Your exact column name.

[v-ssriganesh]

Hello @jaryszek,
Thank you for posting your query in the Microsoft Fabric Community Forum. Here’s how you can achieve this in Deneb:

Prepare your data with a “Rank” field that categorizes each bar (e.g: 1, 2, 3, 4, 5) and In your Deneb visual’s Vega-Lite JSON, set up a color scale that directly relates each rank value to a specific hex color. The legend will reflect these specific assignments.

Example json code:

"color": {
  "field": "Rank",
  "type": "nominal",
  "scale": {
    "domain": [1, 2, 3, 4, 5, "Others"],
    "range": ["#E81123", "#107C10", "#0078D4", "#FFB900", "#8E8CD8", "#B3B3B3"]
  },
  "legend": {
    "title": "Rank",
    "labelExpr": "datum.value == 'Others' ? 'Others' : 'Rank ' + datum.value"
  }
}

Please give it a try and let me know if you encounter any issues.

Best regards,
Ganesh Singamshetty.

[jaryszek]

Thank you very much, 

i tried with :

{
  "$schema": "https://vega.github.io/schema/vega-lite/v5.json",
  "data": {"name": "dataset"},
  "transform": [
    {
      "calculate": "(datum['Rank (Current Axis)'] == null || datum['Rank (Current Axis)'] > 5) ? 'Others' : toString(datum['Rank (Current Axis)'])",
      "as": "RankBucket"
    }
  ],
  "mark": {"type": "bar", "tooltip": true},
  "encoding": {
    "x": {
      "field": "Date",
      "type": "temporal",
      "timeUnit": "yearmonthdate",
      "axis": {"labelAngle": -40, "title": null}
    },
    "xOffset": {
      "field": "MeterCategory",
      "type": "nominal",
      "title": null
    },
    "y": {
      "field": "Sum of CostInBillingCurrency",
      "type": "quantitative",
      "aggregate": "sum",
      "axis": {"title": null}
    },
    "color": {
      "field": "RankBucket",
      "type": "nominal",
      "scale": {
        "domain": ["1","2","3","4","5","Others"],
        "range": ["#E81123","#107C10","#0078D4","#FFB900","#8E8CD8","#B3B3B3"]
      },
      "legend": {
        "title": "Rank",
        "labelExpr": "datum.value === 'Others' ? 'Others' : 'Rank ' + datum.value",
        "orient": "right"
      }
    },
    "order": {"field": "MeterCategory"}
  },
  "config": {
    "view": {"stroke": null},
    "axisY": {"grid": true},
    "bar": {"size": 10}
  }
}

but have no idea how to make this working. 

Can i use dynamic color range from power bi measure instead of this ?:

"color": {
  "field": "Rank",
  "type": "nominal",
  "scale": {
    "domain": [1, 2, 3, 4, 5, "Others"],
    "range": ["#E81123", "#107C10", "#0078D4", "#FFB900", "#8E8CD8", "#B3B3B3"]
  },

Best wishes,
Jacek

[dm-p · Accepted solution]

Hi @jaryszek,

You can assign a value to a color encoding by using the field operator in a scale's range after specifying the field to use for its domain, e.g.:

{
  ...
  "encoding": {
    ...
    "color": {
      "field": "MeterCategory",
      "scale": {
        "range": {"field": "Color by Rank"}
      },
      ...
    },
    ...
  },
  ...
}

This is also covered in the Vega-Lite documentation, with an example available here.

Full spec (based on yours above and corrected to work for this case). The visual data set consists of:

• Date (x encoding)
• Sum of CostInBillingCurrency (y encoding)
• MeterCategory (color encoding)
• Color by Rank (color range)

{
  "data": {
    "name": "dataset"
  },
  "mark": {
    "type": "bar",
    "tooltip": true
  },
  "encoding": {
    "x": {
      "field": "Date",
      "type": "temporal",
      "timeUnit": "yearmonthdate",
      "axis": {
        "labelAngle": -40,
        "title": null
      }
    },
    "y": {
      "field": "Sum of CostInBillingCurrency",
      "type": "quantitative",
      "axis": {
        "title": null
      }
    },
    "color": {
      "field": "MeterCategory",
      "scale": {
        "range": {"field": "Color by Rank"}
      },
      "legend": {
        "title": "Rank",
        "labelExpr": "datum.value === 'Others' ? 'Others' : 'Rank ' + datum.value",
        "orient": "right"
      }
    },
    "order": {
      "field": "MeterCategory"
    }
  },
  "config": {
    "view": {
      "stroke": null
    },
    "axisY": {
      "grid": true
    },
    "bar": {
      "size": 10
    }
  }
}

I've also attached a copy of your workbook, along with this working visual.

Thanks for using Deneb!

Daniel

[jaryszek]

Wow, you are the boss!!

Thank you so much, really appreciated.

Best,
Jacek

[v-ssriganesh]

Hello @jaryszek,
No, Deneb cannot use a Power BI measure (like [Rank (Current Axis)]) for the color range. Measures are not available in the Deneb dataset only table columns are.

Use Vega-Lite transforms (like aggregate + window) to calculate rank and assign colors dynamically no DAX needed.

Thank you,
Ganesh Singamshetty.

[dm-p]

Hi @v-ssriganesh,

To clarify, measures absolutely can be used in a Deneb dataset. Deneb will aggregate measures by all grouping columns, just like a core table visual does. This problem can be solved either internally to the visual spec or using a measure. I have provided a solution using the OP's desired measure in the visual dataset above.

Cheers,

Daniel

[v-ssriganesh]

Hello @dm-p,
Thanks for sharing your valuable insights.

Best Regards,
Ganesh Singamshetty.