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 stri...
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

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