Derive Column based on Slicer selection

[Peter_Jeyaraj_I]

Hi All, 

I want to change the dimension selection in a chart based on the slicer filter in POwer bi.. For ex.. i have a mapping with 2 different description per language like below

& i created a language table with 1 column called "language" with the contents "english" and "danish". 

Now, my request here is that if i select english then my chart will show the info based on fail_category column else "fail_category_D" column.. 

I tried with selectedvalue function and other options but nothing works.. Please help to rectify this issue & let me know if you need any more details.. 

[v-frfei-msft]

Hi @Peter_Jeyaraj_I,

We can use bookmark to work around. 

https://docs.microsoft.com/en-us/power-bi/desktop-bookmarks

I made one sample for your reference.

Attached the pbix as well.

Regards,

Frank

[Peter_Jeyaraj_I · Accepted solution]

 thank you.. i created the many to many releationship by using the bridge table. 

this way i created the relationship b/w my transaction to fail code mapping with duplicate entries ( 1 entry for eng and 1 entry for danish).  that solved my issue.. 

more details refer below link

https://www.seerinteractive.com/blog/join-many-many-power-bi/

[v-frfei-msft]

Hi @Peter_Jeyaraj_I,

That's cool, Kindly mark your answer as a solution to close the case please. Thanks in advance.

Regards,

Frank

[BobBI]

Hi Peter,

how your language table is connected with base data table. , there can also achieve this using this approach.

Name of the table is Date(2) .

- Unpivot your table for Fail_Category and Fail_category_D

- create measure 'Count' = COUNT('Data (2)'[Value])

- drag object 'Value' to Axis and measure Count into Value

Slicer :

Create slicer using 'Value' field.  ( you can change the name for category to english and Danish if needed)

 

Hope this helps

SS

[PattemManohar]

@Peter_Jeyaraj_I Could you please post your expected output as well ? If possible sample data that can be copied. 

[Peter_Jeyaraj_I]

Here below i shared the sample screenshot

I have 3 different tables

1. Master ( Countof Failures measure available here )

2. Fail_Mapping ( Fail category & fail category d columns available here)

3. Language ( Lang field )

If i select english then the chart has to show the failures count by fail category 

If i select danish then the chart has to show the failures by fail category d. Hope this helps

[PattemManohar]

@Peter_Jeyaraj_I Here is the steps I've followed to solve this..

Create a new calculated table from your main table as below:

Test21ChartNew = UNION(
                        SELECTCOLUMNS(Test21Chart,"Code",[Code],"FailCategory",[FailCategory])
                       ,SELECTCOLUMNS(Test21Chart,"Code",[Code],"FailCategory",[FailCategory_D]) 
                      )

The output will be as :

Similary, create a lookup table as below

Test21ChartSlicer = DISTINCT(
                UNION(
                        SELECTCOLUMNS(Test21Chart,"Language","English","FailCategory",Test21Chart[FailCategory])
                        ,SELECTCOLUMNS(Test21Chart,"Language","Danish","FailCategory",Test21Chart[FailCategory_D])
                    )
                )  

This will be result to as below:

Now make sure these two tables have relationship on FailCategory field. That's it, now you have your output as expected.

[Peter_Jeyaraj_I]

Thanks for your response. 

but i need to maintain the unique values in fail code table as i have code - code relationship b/w master table( kind of transaction table) and fail code table. 

that is the reason why i created new columns to accomadate other language values.