Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Peter_Jeyaraj_I
Helper II
Helper II

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

 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
v-frfei-msft
Community Support
Community Support

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.

 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/

 

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.
BobBI
Resolver III
Resolver III

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

PattemManohar
Community Champion
Community Champion

@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 PBI Community Champion




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

@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 PBI Community Champion




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. 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors