topic LOOKUPVALUE LOOP to create a multilingual report in DAX Commands and Tips

LOOKUPVALUE LOOP to create a multilingual report

Anonymous — Fri, 02 Dec 2022 12:12:40 GMT

Hello everybody! 🙂

Im creating a multilingual report, and i need to create a Dax Measure called "Products" (in this case) that I insert into a table with the intention that when I change the USERCULTURE() in the Service, it will change the product description in the table showing the corresponding translation.

I have a table called 'Data' and its columns are [Desc], [Code], [Language] (as picture).

So when I created the Dax Measure I used a LOOKUPVALUE formula.

Products = LOOKUPVALUE('Data'[Desc],Data[Code],LOOKUPVALUE(Data[Code],Data[Desc],LOOKUPVALUE(Data[Code],Data[Desc],CALCULATE(MIN(Products[Desc]))),Data[Language], [Selected_Language])) *Products[Desc] is a calculated table that I created but I don't want to create anything else unless it is mandatory for it to work

But when I insert into a table i have no results.

My problem is that when the Data[Code] is unique I have no problems (because I write the literal string in the function), but with the products its different because the [Code] change for every Product (e.g Product1, Product2, ..., Product 15).

report_title = LOOKUPVALUE(TitleTraductions[Desc], TitleTraductions[Code],"report_title", TitleTraductions[Language], [Selected_Language])

So I would like if any of you could help me to approach it in another way that I'm not seeing.

The others metrics that I need like 'Countries' or 'Status' will be the same but adapted for each case, so for now I will be so happy if you help me with the 'Products' case.

This is the metric that I use for chage the language in the service:

Selected_Language = LEFT(USERCULTURE(),2)

Any question that you need to understand more this case, tell me with no problems.

Thank you so much in advance,

Regards,

Re: LOOKUPVALUE LOOP to create a multilingual report

v-yadongf-msft — Mon, 05 Dec 2022 02:59:18 GMT

Hi Anonymous ,

This is my test table:

Create two measures:

Selected_Language = LEFT(USERCULTURE(),2) Products = IF(ISINSCOPE('Data'[Code]),MAXX('Data',IF([Selected_Language] = 'Data'[Language],'Data'[Desc])),BLANK())

Create a table:

If I change the language to italian:

The products will change according the Service language:

Best regards,

Yadong Fang

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

Re: LOOKUPVALUE LOOP to create a multilingual report

Anonymous — Thu, 08 Dec 2022 11:24:27 GMT

v-yadongf-msft

Thank you so much for your answer, this looks like this is gonna be the best solution.

But right now im having problems creating the table with me measure ('Products') and the field Data[Code].

I dont know why its not working, because look very similar what you create and what I have (I think its my dataset). But i duplicate the Data table and filteres just for products but still not working

https://drive.google.com/file/d/16W6RU14R1sfyFzFXxw74VUP2E5DQpa40/view?usp=sharing

Here you can find the .pbix file if you think its better to solve it 🙂

Again, thank you so much!

Re: LOOKUPVALUE LOOP to create a multilingual report

lbendlin — Sat, 10 Dec 2022 14:45:39 GMT

You have an issue in your Data table - there is a leading space in the Language column.

I would recommend you look into Field parameters, or into creating separate reports. Your effort is impressive but I don't think this is maintainable.

See attached for the changes.

Re: LOOKUPVALUE LOOP to create a multilingual report

Anonymous — Tue, 13 Dec 2022 10:59:48 GMT

Thank you lbendlin for your help, That was exactly what i needed to get the measure working!!

I know that is gonna be very difficult to do something useful for everyone, but i think i could do something good for particular cases that need a multilingual report.

Thank you!

Re: LOOKUPVALUE LOOP to create a multilingual report

Itsbibin — Sun, 08 Oct 2023 20:57:08 GMT

Hi v-yadongf-msft

Ihave 2 table in my Power BI report B-Details and SPResult. SP result table is a stored procedure and will change dynamically with parameters. I want to create a new table from this in which it should filter Name and Country from B-Details and SPResult. Now after filtering this we have a uniquekey in B-Details. Now in the new table for each UniqueKey we have to get all the filtered values from SPResult table. So in the new table UniqueKey key row will be duplicated with as much rows we have in SPResult. Can you help me how to fix this?