Forum Discussion

powerbiexpert22's avatar
powerbiexpert22
Icon for Impactful Individual rankImpactful Individual
3 days ago
Solved

Format function causing blankbar chart

i am using format function to conditionally display units on bar chart however it shows empty bar chart when i include below code, it is working fine in table visual, i cannot use dynamic format string since i am using live connection to dataset

 

Need Help
power bi
  • Cookistador's avatar
    Cookistador
    3 days ago

    Hello powerbiexpert22​ 

    The format function convert your measure from Numeric value to a text
    A table can display Text but not your chart

    I see two options to fix your issues:

    If you have permission to modify the semantic model, implement a dynamic format string in the model (best solution).

    Create two measures 

    Value Measure = [PCD CY Actuals USD] --> For your chart 

Formatted Measure =  --> For your table
VAR CYActuals = [PCD CY Actuals USD]
RETURN
SWITCH(
    TRUE(),
    CYActuals < 1000000, FORMAT(CYActuals,"#,.0K"),
    CYActuals < 1000000000, FORMAT(CYActuals,"#,,.0M"),
    FORMAT(CYActuals,"#,,,.0B")
)

    Then in your chart:
    Format pane → Y-axis → Display units

5 Replies

  • Cookistador's avatar
    Cookistador
    Icon for Super User rankSuper User
    3 days ago

    Hello powerbiexpert22​ 

    The format function convert your measure from Numeric value to a text
    A table can display Text but not your chart

    I see two options to fix your issues:

    If you have permission to modify the semantic model, implement a dynamic format string in the model (best solution).

    Create two measures 

    Value Measure = [PCD CY Actuals USD] --> For your chart 

Formatted Measure =  --> For your table
VAR CYActuals = [PCD CY Actuals USD]
RETURN
SWITCH(
    TRUE(),
    CYActuals < 1000000, FORMAT(CYActuals,"#,.0K"),
    CYActuals < 1000000000, FORMAT(CYActuals,"#,,.0M"),
    FORMAT(CYActuals,"#,,,.0B")
)

    Then in your chart:
    Format pane → Y-axis → Display units

    • powerbiexpert22's avatar
      powerbiexpert22
      Icon for Impactful Individual rankImpactful Individual
      2 days ago

      Hi Cookistador​ ,

      for option1, how to apply dynamic format string at semantic model level in this case, can you provide an example?

      for option2, i do not see an option to apply formatted mesure under Format Pane -> Y Axis -> Values  

       

      • Cookistador's avatar
        Cookistador
        Icon for Super User rankSuper User
        2 days ago

        Select you measure and in format select Dynamic

         

        In the formula bar, pass [Formatted Measure] or the following code 

        VAR CYActuals = ABS ( SELECTEDMEASURE () )
RETURN
SWITCH(
    TRUE(),
    CYActuals < 1000000, "#,0,.0 K", 
    CYActuals < 1000000000, "#,0,,.0 M",
    "#,0,,,.0 B" 
)

        and now in your visual, you should have the desired ouput

         

  • powerbiexpert22's avatar
    powerbiexpert22
    Icon for Impactful Individual rankImpactful Individual
    2 days ago

    Hi Cookistador​ , is there a way to apply this formatting at report level , i do not want to apply this formatting at semantic model level since it may impact other reports

    • v-achippa's avatar
      v-achippa
      Icon for Community Support rankCommunity Support
      12 hours ago

      Hi powerbiexpert22​,

      Dynamic format strings cannot currently be applied to report-level measures in a live-connected report. If you do not want to modify the shared semantic model, use the original numeric measure in the chart and configure the Y-axis Display units to Auto or the required unit. This setting applies only to that visual.

      For reference, follow the below documentation:
      Create dynamic format strings for measures - Power BI | Microsoft Learn

      Thanks and regards,
      Anjan Kumar Chippa