Is it possible to translate the items in a Calculation group

[mariussve1]

Hi,

I'm creating a multilanguage report in Power BI and using "Translation Builder" and "Tabular Editor 3" as a tool for doing this. I can translate measures, tables and so on, but I have not yet found a way to translate the items inside a Calculation group.

Lets say I have a Calculation group called Time Intelligence with this items:
Last month
Last year
Year to date
Year to date Last Year
and so on

Then I use this items as a legend in a visual, so I can compare last month or ytd with last year. How can I translate this items?

[OwenAuger · Accepted solution]

@mariussve1 

1. Yes, RLS would certainly be an option, similar to your example 🙂

You would have to have all users included in the role in the Power BI Service (using an appropriate group).

You could set up a Language table as you've shown, with relationship to the field parameter table which has an appropriate Language column added.

Here's my version:

2. Alternatively, you could use the Preselected Slicer visual to do the same job without RLS.

I've attached my test PBIX in case it's useful for comparison 🙂

[mariussve1]

Hi again @OwenAuger,

I’ve been working on implementing translations for different languages using RLS but have encountered a couple of challenges. If you’re able to help me, I’d really appreciate it. (Or if there’s anyone else on this forum who can assist).

First challenge: Is it possible to select only certain "Items" from a "Calculation group"?
For example, if I only want to display "Last month" and "Last year," how can I filter this while still retaining the translations?

Second challenge: If I add a new "Item" to display a percentage, for example YoY pct change:
[New value] - [Old value] / [Old value]
And then format it as a percentage: #,0.00%

It doesn’t seem to work as expected, as it only shows the original value ( SELECTEDMEASURE() ).

I hope you can take a look at this with me so I can resolve these challenges and reach the finish line 🙂

Thank you so much in advance!

[OwenAuger · Accepted solution]

Hi again @mariussve1,

Good to hear from you 🙂

I have attached an updated PBIX illustrating how I would handle the two challenges you mentioned.

Note on Group By Columns:

When updating my PBIX, I noted that the "Group By Columns" property of the translated calculation group columns may not have been set correctly (apologies if this was the case in the original PBIX).

In Tabular Editor, 'Time Intelligence'[Time Calc (es)] and 'Time Intelligence'[Time Calc (zh)] should both have Group By Columns set to 'Time Intelligence'[Time Calc (en)].

First challenge

Yes, you can indeed filter a calculation group so that only certain items are included in visuals, at a visual, page, or report level.

As an example, I have applied a report level filter on the English Time Calc column so that Last month, Last year and YoY pct change are the only calculation items selected.

Second challenge

You can certainly create a calculation item that returns YoY % change or similar.

I would write the expression like this. The code from calculation items Selected Period and Last year is restated here to avoid sideways recursion. A similar example is shown in this article:

https://www.sqlbi.com/articles/controlling-format-strings-in-calculation-groups/

VAR SelectedPeriod =
    SELECTEDMEASURE ()
VAR LastYear =
    CALCULATE (
        SELECTEDMEASURE (),
        SAMEPERIODLASTYEAR ( 'Date'[Date] )
    )
VAR Result =
    DIVIDE (
        SelectedPeriod - LastYear,
        LastYear
    )
RETURN
    Result

The format string expression is:

"#,0.00%"

Hopefully that is some help. Please post back if needed or if I haven't quite addressed the challenges as you expected 🙂

Regards

[mariussve1]

Hi,

Sorry for the late reply, but thank you so much. This worked 100%. It was the ‘group by’ that caused all the trouble, but now it works exactly as desired!

Absolutely great 👌😃

[OwenAuger · Accepted solution]

@mariussve1 

1. Yes, RLS would certainly be an option, similar to your example 🙂

You would have to have all users included in the role in the Power BI Service (using an appropriate group).

You could set up a Language table as you've shown, with relationship to the field parameter table which has an appropriate Language column added.

Here's my version:

2. Alternatively, you could use the Preselected Slicer visual to do the same job without RLS.

I've attached my test PBIX in case it's useful for comparison 🙂

[mariussve1]

Hi again,

I see you mention that I cannot hide the "Preselected Slicer visual"? Is there any reason why I can't do that?

But I think RLS is a better choice, then I dont need to have this slicer in every report. Im using embedded version of Power BI, so I only have one appuser to put into rls role 🙂

[OwenAuger]

Hi again @mariussve1 

It sounds like RLS would work well for you then! 🙂

Regarding the Preselected Slicer visual, what I meant was, if it is truly hidden using the selection pane, it functions unreliably (unlike the core slicer visuals). Specifically, I have found that it briefly works when first hidden, then doesn't filter as expected after a few interactions with the report (at least in Power BI Desktop).

Regards,

Owen

[OwenAuger]

Hi @mariussve1 

Since the names of Calculation Items are values in a column, I would suggest following these steps:

1. Add additional columns in the Calculation Group table containing translations of the Calculation Items for each language required.
   These columns would have to be created as calculated columns with DAX, and you would need to set the "Group By Columns" property of the translated columns to be the original Calculation Item column.
   See my blog post here on this topic.
2. Set up a field parameter to control the selection of the appropriate Calculation Group column to use in visuals, along the lines of this guide:
   https://learn.microsoft.com/en-us/power-bi/guidance/data-translation-implement-field

I could create a small example to demonstrate when I have a chance (if that's useful), otherwise did you have a sample PBIX you could share?

Regards

[mariussve1]

Hello @OwenAuger,

And thank you so much for your quick reply. I did actually follow this:

https://owenaugerbi.com/creating-alias-columns-for-calculation-groups/

Looks like its the same concept. But, how can I be able to show the correct language using USERCULTURE() so the endusers dont need to choose the correct language?
Do I need to setup this in RLS on the table so it automaticly filters? Or is it something else and better that Im missing.

I truly appreciate the help, thank you so, so much!!

[mariussve1]

Looks like RLS could solve this, but maybe something else is better?