Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

FORMAT function not working in line charts

Hi everyone!

 

I´m struggling with the following issue:

I´m creating a report in which the user may choose the display currency for all revenues. I added a currency conversion table to the underlying tabular model, inserted a slicer for the desired currency to the report and created the following revenue measure:

revenue_dynamic_curr:=
FORMAT(
    SUM(example_table[revenue])
        * IF(HASONEVALUE(currency_exchange_table[target_currency]), max(currency_exchange_table[exchange_rate]), 1),

    IF(HASONEVALUE(currency_exchange_table[target_currency]), max(currency_exchange_table[currency_format]), "#,##0 €;(#,##0 €)")
)

As you can see, Eurois the default currency; if another metric is chosen, the respective exchange rate will be applied and the currency sign will change.

 

This formula works perfectly fine as long as I only use it in tables.

When I try to use it in a line or bar chart, however, nothing happens. I can drag it to the "Values" bin as often as I want to, nothing happens. The issue doesn´t lie in missing relationships or something similar, however, as the measure will work in the "Tooltip" bin when I use another measure in the "Values" bin.

 

Does anyone else have experience with this and can help me out? I´d appreciate that greatly!

1 ACCEPTED SOLUTION

Hi Eric,

 

I'm afraid there isn't a good solution for now. Please refer to this post.

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-jiascu-msft
Microsoft Employee
Microsoft Employee

Hi @Anonymous,

 

That's because the measure return a non-numeric value which can't be added in the "Values" of the line chart. How about use the built-in currency format?

FORMAT_function_not_working_in_line_charts

 

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi @v-jiascu-msft,

 

thanks for your response! Unfortunately, I can´t make use this functionality because in my specia use case the format is determined by the underlying data source and may not be changed within PowerBI. But even if this was possible, I don´t think that I´d reach my goal of changing the currency format dynamically by doing so since you can only pick one currency at a time. Please correct me if I´m wrong with this.

The solution I´m looking for should allow for a dynamic change from e.g. € to $ etc.

 

I find it somehow strange that metric whose calculation I posted works in tables and even in tooltips of line and bar charts but not as th "values" of either line or bar chart. Doyou have any further ideas on this?

 

Best,

Eric

Hi Eric,

 

I'm afraid there isn't a good solution for now. Please refer to this post.

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi Dale,

 

it´s not the solution I was hoping for but was already afraid that it might not be possible to do what I intended...

Thanks for your efforts anyway!

 

Best,

Eric

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.