Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Tabular Editor - Calculation group breaking

Hello,   When I add this DAX to a calulation item in a calculation group, it works temporarily then borks the entire calculation group. When selected from a slicer, this calculation returns 'An Une...
Need Help
Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hello v-henryk-mstf 

 

Thanks for the response. I've read through the linked article and it doesn't provide any answers to my current issue.

 

I'll provide a step-by-step of what I did below, but first I'll outline the problem I am trying to solve in case there is an alternate solution:

 

Any power bi report for my company that includes currency values needs to have the values reported in 3 differenct currencies: GBP, USD, EUR. These used fixed rate mofidiers (GBP*1.34 for EUR, GBP*1.6 for USD) which makes it a bit simpler than live currency rates.

 

Creating sales reports means a lot of currency related measures. Calculation groups in tabular editor are ideal for reducing the complexity and logic duplication for converting these measures into USD/EUR.

 

However, there are multiple visuals that will includes currency measures and non-currency measures (eg. revenue and percent to target in the same visual). I am trying to find a way to have calculation items affect specific measures in a visual/across a report. Naming each measure specifically will be time consuming and require a lot of upkeep so I wanted a faster way to do this. I thought that checking the current formatting of the measure was a neat solution because anything that is already formatted as a currency should be treated as such by these calculation items.

 

Here are the steps I took to try to put this into action (i tested this in a new desktop report to remove any report specific variables):

  1. Create blank power bi desktop and load in a table (used Date dimension)
  2. Create a measure – Sum_Val, format as GBP/default currency
  3. Open tabular editor, create calculation group called ‘Currency Formatter’, add calculation item called USD
  4. Enter the DAX Expression 
  5. Save in tabular editor and refresh on report page
  6. Add visuals to show the measure value and the Currency Formatter as a slicer
  7. Select the Slicer option USD. A CMD prompt briefly appears then the following error is given on the visual
  8.  This also seems to perenially break the tablular editor calculation group functionality. E.g. If i add a new Calcultion item or edit and existing one to SELECTEDMEASUREFORMATSTRING() to try and see the formatting of the measure, I still get the original measure value and not the format string

Thanks in advance for any help and advice