Deneb - Issue with conditional formatting for measure

Deneb Showcase
1

Hello,
I am trying to replicate in DENEB the same dynamic formatting I am applying to my measure in PowerBI. (so thousands with K and millions with MM)
However, my approach is clearly not the right one…
Code used is below, what I am getting out of it is
[object Object]
instead of the numbers.

Can somebody help me understand what I am doing wrong? Thanks!
Kind regards
Valeria

  "encoding": {
    "x": {
      "field": "Spending & Forecast",
      "type": "quantitative"
    },
    "opacity": {
      "condition": [
        {
          "test": {
            "field": "__selected__",
            "equal": "off"
          },
          "value": 0.3
        }
      ],
      "value": 1
    },
    "text": {
      "field": "Spending & Forecast",
      "format": {
        "condition": [
          {
            "test": "datum['Spending & Forecast']>=1000000",
            "value": "#,,.0 MM"
          }
        ],
        "value": "#,,.0 K"
      },
      "formatType": "pbiFormat"
    }
  }
2

Hi @valeriabreveglieri.

I’ve not seen a condition block used in the format block before. To date, In cases like these, I’ve often used the built-in Vega-Lite formatting (rather than the Power BI formatting) with a stand-alone transformation to create the string I want, then reference that. Vega-Lite is no different that Power BI in that there are often many different ways to accomplish the same thing. Here’s one:

eDNA Forum - Deneb Text Label Conditional Format - 1
eDNA Forum - Deneb Text Label Conditional Format - 12417×1606 285 KB

Here’s the code:

{
  "data": {"name": "dataset"},
  "transform": [
    {
      "calculate": "format( datum['Amount'], '~s' )",
      "as": "_amount"
    }
  ],
  "encoding": {
    "y": {
      "field": "Company",
      "type": "nominal"
    },
    "x": {
      "field": "Amount",
      "type": "quantitative",
      "axis": {"title": "Amount"}
    }
  },
  "layer": [
    {
      "mark": {
        "type": "bar",
        "tooltip": true
      }
    },
    {
      "name": "ORIGINAL",
      "mark": {
        "type": "text",
        "xOffset": 10,
        "yOffset": -10,
        "align": "left"
      },
      "encoding": {
        "text": {
          "field": "Amount",
          "type": "nominal"
        }
      }
    },
    {
      "name": "FORMATTED",
      "mark": {
        "type": "text",
        "xOffset": 10,
        "yOffset": 10,
        "align": "left"
      },
      "encoding": {
        "text": {
          "field": "_amount",
          "type": "nominal"
        }
      }
    }
  ]
}

Here’s the reference URL:

Hope it helps.
Greg
eDNA Forum - Deneb Text Label Conditional Format.pbix (1.3 MB)

3

Hello Greg
thanks!!! I actually started like this but my company has very precise requirements - for ex. millions is MM and not M… whereas the s D3 format is not configurable.
My DAX format for the measure is:
SWITCH (
TRUE (),
SELECTEDMEASURE () < 1000, “$#,##0”,
SELECTEDMEASURE () < 1000000, “$#,##0,.0K”,
“$#,##0,.0MM”
)

so I was trying to replicate that…
so you don’t think this is doable in DENEB?

Thanks!!!
Kind regards
Valeria

4

Anything is doable in Deneb … I’ll take another stab later today.

5

Hi @valeriabreveglieri.

Here’s another go:

eDNA Forum - Deneb Text Label Conditional Format - 2
eDNA Forum - Deneb Text Label Conditional Format - 23038×1560 369 KB

Here’s the code:

{
  "data": {"name": "dataset"},
  "transform": [
    {
      "calculate": "datum['Amount'] < 1000 ? format( datum['Amount'], '$,.2r' ) : datum['Amount'] < 1000000 ? format( datum['Amount']/1000, '$,.2r' ) + 'K' : format( datum['Amount']/1000000, '$,.2r' ) + 'MM'",
      "as": "_formatted_text_amount"
    }
  ],
  "encoding": {
    "y": {
      "field": "Company",
      "type": "nominal"
    },
    "x": {
      "field": "Amount",
      "type": "quantitative",
      "axis": {"title": "Amount"}
    }
  },
  "layer": [
    {
      "mark": {
        "type": "bar",
        "tooltip": true
      }
    },
    {
      "name": "ORIGINAL",
      "mark": {
        "type": "text",
        "xOffset": 10,
        "yOffset": -10,
        "align": "left"
      },
      "encoding": {
        "text": {
          "field": "Amount",
          "type": "nominal"
        }
      }
    },
    {
      "name": "FORMATTED",
      "mark": {
        "type": "text",
        "xOffset": 10,
        "yOffset": 10,
        "align": "left"
      },
      "encoding": {
        "text": {
          "field": "_formatted_text_amount",
          "type": "nominal"
        }
      }
    }
  ]
}

Hope it helps.
Greg
eDNA Forum - Deneb Text Label Conditional Format - v2.pbix (1.3 MB)

1 Like
6

Hi Greg,
this is such a nice solution!!! Thank you so much. I did not know you could add the suffix the way you did - I learned something very useful today :slight_smile:
As I was at it, another question came to my mind. Now I have the text of the bars formatted, but the axis is still not formatted the way the company wants.
How would you go for it? I was thinking of using “labelExpr”, but as the formatted text is not a number any longer I get $NaN in the axis… is there a way to accomplish this?

Thanks again for all your help!
Kind regards
Valeria

7

Hi @valeriabreveglieri. Yup, using labelExpr in the X-axis block is the way to achieve this. For calculations inside the axis, however, instead of the data point, you need to access the axis value, so reference the datum.value. Here’s my implementation:

eDNA Forum - Deneb Text Label Conditional Format - 3
eDNA Forum - Deneb Text Label Conditional Format - 32966×1131 275 KB

Here’s the code block:

    "x": {
      "field": "Amount",
      "type": "quantitative",
      "axis": {
        "title": "Amount",
        "labelExpr": "datum.value < 1000 ? format( datum.value, '$,.2r' ) : datum.value < 1000000 ? format( datum.value/1000, '$,.2r' ) + 'K' : format( datum.value/1000000, '$,.2r' ) + 'MM'"
      }
    }

Hope it helps.
Greg
eDNA Forum - Deneb Text Label Conditional Format - v3.pbix (1.3 MB)

1 Like
8

Thanks Greg!!! I was definitely missing this. Another lesson learned today :slight_smile: . Thank you so much for taking the time to answer and teach me!!!
Kind regards
Valeria

9

Hello Greg,

Is there a possible way to have a dynamic currency for this example?

10

Hi @sgrady,

Welcome to the forum!

we would like to encourage you to create a separate thread for your questions related to this inquiry so that other users can easily check the details of your queries.

For further details can check it out here - Asking Questions On The Enterprise DNA Support Forum

1 Like
11

Hi @valeriabreveglieri

Did @Greg’s respnse help solve your query?

If not, can you let us know where you’re stuck and what additional assistance you need?

If it did, please mark the answer as the SOLUTION by clicking the three dots beside Reply and then tick the check box beside SOLUTION

Thank you

12

yes it did! Thanks for letting me know how to mark it as a solution - it’s done now.
Kind regards
Valeria