Dynamic TEXT columns depend on slicer select

[RobertO995]

Hello all,

I got a problem with dynamic columns in POWER BI. The thing is that is the text. I have googled it, but cannot find answer.

I got table like this:

 this is product code, name and translation in a few languages for its name.

Also I got another one table with product code and sold amount. The sales table is linked to this table by product code.

I want to create a visualization where I can select language on slicer and see:

Product name in selected language - Sold amount.

I have never faced with problem like this, because in that case I have translations in columns, not in rows. I found a solution, where I got table like this:

product number 1 - eng name

product number 1 - german name

product number 1 - russian name

product number 2 - eng name

product number 2 - german name

.... etc

But it creates too many rows in my model.

Could you possibly help me find a solution? I still want to have translations in columns.

Thank you community!

[v-lili6-msft · Accepted solution]

hi, @RobertO995

First, you should know that 

1. Calculation column/table not support dynamic changed based on filter or slicer.
2. Measure can be affected by filter/slicer, so you can use it to get dynamic summary result.

here is reference:

https://community.powerbi.com/t5/Desktop/Different-between-calculated-column-and-measure-Using-SUM/t...

https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

Second, since your names are in different columns, and you want to dynamic change it, you had to aggregate them into one column. so they will get many rows in my model.

and you may try this way:

In Edit Queries, Duplicate the basic data column, then Unpivot the name columns.

Close&Apply

Then create a relationship between two table by ID column

Then you could use Attribute and Value from duplicate table as dynamic filter.

By the way, you'd better darg product code column into visual too, since if there two or more product code have blank name, they will be aggregated.

product code - Product name in selected language - Sold amount

Best Regards,
Lin

[v-lili6-msft · Accepted solution]

hi, @RobertO995

First, you should know that 

1. Calculation column/table not support dynamic changed based on filter or slicer.
2. Measure can be affected by filter/slicer, so you can use it to get dynamic summary result.

here is reference:

https://community.powerbi.com/t5/Desktop/Different-between-calculated-column-and-measure-Using-SUM/t...

https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

Second, since your names are in different columns, and you want to dynamic change it, you had to aggregate them into one column. so they will get many rows in my model.

and you may try this way:

In Edit Queries, Duplicate the basic data column, then Unpivot the name columns.

Close&Apply

Then create a relationship between two table by ID column

Then you could use Attribute and Value from duplicate table as dynamic filter.

By the way, you'd better darg product code column into visual too, since if there two or more product code have blank name, they will be aggregated.

product code - Product name in selected language - Sold amount

Best Regards,
Lin

[RobertO995 · Accepted solution]

Hello,

thank you for your response. It could be a good solution, but there is a couple of things that can make it harder to work propely. 

First thing is that in table in translations I have only product numbers (it's called SKU PLU in my table) and their translations to other 10 languages. There is no data values like sales, markdowns etc. It's only kind of dictionary. In that 'dictionary' product number is unique value. I have also second table with value of markdown, type of markdown, store number and product number (SKU PLU) it concerns. As you can imagine - in that table product number can be repeated many times. Table with 'values' has about 1,700,000 rows. It's nothing strange, because I seperate every kind of discount to every product and every store.

These two tables are linked by one-to-many relationship (dictionary to discount table).  If I add new table as you wrote I will get one to many relationship between these two "dictionary" tables and one to many relationship between markdown and dictionary table. It will present sum of values in every row. 

Could you please answer me, if is there a way, some kind of workaround to deal with it? Or the only option is to add markdown values to dictionary table? It will probably kill my model with too much data issue...

Thanks!

[v-lili6-msft]

hi, @RobertO995

That's the usual way to do it, Unpivot the name columns in Edit Queries, unless you create a measure.

But for your requirement, measure does not apply to it.

So the best option is to add markdown values to dictionary table.

Best Regards,

Lin