cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted

Derive Column based on Slicer selection

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

 

Capture.PNG

 

& 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.. 

1 ACCEPTED SOLUTION

Accepted Solutions

Re: Derive Column based on Slicer selection

 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/

 

View solution in original post

8 REPLIES 8
Super User I
Super User I

Re: Derive Column based on Slicer selection

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



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





Re: Derive Column based on Slicer selection

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

 

Capture1.PNG

BobBI Resolver III
Resolver III

Re: Derive Column based on Slicer selection

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)

 

data.JPGunpivot.JPGoutput.JPG 

Hope this helps

SS

Super User I
Super User I

Re: Derive Column based on Slicer selection

@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 :

 

image.png

 

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:

 

image.png

 

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

 

image.pngimage.png

 

 



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





Re: Derive Column based on Slicer selection

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. 

Community Support
Community Support

Re: Derive Column based on Slicer selection

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.

 

bookmark1.PNGbookmark2.PNG

 

Attached the pbix as well.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Re: Derive Column based on Slicer selection

 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/

 

View solution in original post

Community Support
Community Support

Re: Derive Column based on Slicer selection

Hi @Peter_Jeyaraj_I,

 

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

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors