Creating dynamic values Translation by Translation Builder

[UPF1KOR]

Hi,

I am using Translation builder to create Trnaslation for visual lables. However, one label is dynamic means, it gets created based on selection in the PIE chart. Below is the code for you to refer. Problem is, how can I translate this in multiple languages or pass as parameter to Tranlsation builder? 

M_TITLE =

VAR _LD_Selection =

    IF (

        ISFILTERED ( 'Table'[Value1] ),

        COUNTROWS ( VALUES ( Table'[Value1] ) ),

        0

    )

RETURN

    IF (

        _LD_Selection <> 0,

        CONCATENATE (

            CONCATENATE (

                "Deviation Duration (in hrs) on L2 [Within ",

                SELECTEDVALUE ( 'Table'[Value1] )

            ),

            "]"

        )

[UPF1KOR]

Hi @v-jianpeng-msft ,

Thanks a lot for the detailed guide. Your idea is good. Only challenge I see with this approach is the complexity of the DAX query when the number of Language increases. With 4 or 5 languages, Its good. With 31 languages (as in my case) its gonna be a darn hectic query! 
But I appreciate your time & letting me the path to persue. Thanks.

[v-jianpeng-msft · Accepted solution]

Hi, @UPF1KOR 

Translation Builder may not provide dynamic tag translation. There are two ways to do this:

Here's my sample data:

Table:

LanguageTable:

TranslationsTable

Use the following DAX expressions:

M_TITLE_TRANSLATED = 
VAR _LD_Selection =
    IF (
        ISFILTERED ( 'Table'[Value1] ),
        COUNTROWS ( VALUES ( 'Table'[Value1] ) ),
        0
    )
VAR _seleted_value1 = SELECTCOLUMNS('Table',"Value1",'Table'[Value1])
VAR _seleted_code = SELECTEDVALUE(LanguageTable[code])
VAR _English = IF (
        _LD_Selection <> 0,
        CONCATENATE (
            CONCATENATE (
                "Deviation Duration (in hrs) on L2 [Within ",
                LOOKUPVALUE(TranslationsTable[English],'TranslationsTable'[value],_seleted_value1)
            ),
            "]"
        ))
VAR _Spanish = IF (
        _LD_Selection <> 0,
        CONCATENATE (
            CONCATENATE (
                "Deviation Duration (in hrs) on L2 [Within ",
                LOOKUPVALUE(TranslationsTable[Spanish],'TranslationsTable'[value],_seleted_value1)
            ),
            "]"
        ))
VAR _Chinese = IF (
        _LD_Selection <> 0,
        CONCATENATE (
            CONCATENATE (
                "Deviation Duration (in hrs) on L2 [Within ",
                LOOKUPVALUE(TranslationsTable[Chinese],'TranslationsTable'[value],_seleted_value1)
            ),
            "]"
        ))
RETURN SWITCH(_seleted_code,
    1, _English,
    2,_Spanish,
    3,_Chinese)

Use this measure in the card visual. To simulate the dynamics, I put the value1 in the Table into a slicer. At the same time, create a slicer using the code in the LanguageTable, and select dynamic translations according to the different users:

You can also consider using Azure AI Translator. You can refer to the following articles to learn how to use:

Building a multi-language Semantic Model and Report using Translations Builder + Azure AI Translator...

The above similar effect with DAX is for your reference only, you need to make appropriate adjustments according to your actual situation. I've provided the PBIX file used this time below.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

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