Tabular Editor - Calculation group breaking

[Anonymous]

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 Unexpected Exception Occured'. when another calculation from the same clculation group is selected from the slicer, the base value of the SELECTEDMEASURE() is returned, completely unaffected by the measure it has paeed through.

Here is the calculation dax:

IF ( CONTAINSSTRING ( SELECTEDMEASUREFORMATSTRING (), "£" ), format(SELECTEDMEASURE () * 1.6, "$#,0;-$#,0;$#,0"), SELECTEDMEASURE () )

 The idea is that i can easily define currency measures by setting them to GBP currency format. So, when I put Revenue against Quantity in the same visual, it only formats the Revenue and leaves the Quantity as is. It works until it breaks and it breaks if I save from tabular editor for a second time after making this calculation item. The first save has no errors and the calculation item works as intended. If I add similar for a different currency, or change another (independant) measure and save changes in tabular editor, it breaks.

Any idea on why this is breaking the entire calculation group and how to fix?

thanks

[Anonymous]

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

[v-henryk-mstf]

Hi @Anonymous ,

Check the blog may be helpful for your question.

Use Tabular Editor To Create Calculation Groups In Power BI | Enterprise DNA

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

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