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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ChrisDC83
Regular Visitor

Help - Using a Slicer to Change Column Value

I have a need to change what column is shown on charts basis a slicer.  In my data set I have Product Line and Product Name (multiple product names roll up to a single product line) and the users want to be able see their all their report charts break out by either based on what they choose as a slicer.  I have created a "selection" table with single column with the values "Product Line" and "Product Name."  

 

I'm trying to create a variable in my fact table that looks at my selection table and (given whatever the filter is showing) either equals Product Name or Product Line. 

 

Here is my code essentially:  Selection = Switch(Selection_Table[Selection], "PRODUCT NAME", <Fact>'Product Name', "Product Line",<Fact>'Product Line',"") 

 

Even when I have a slicer to and choose a single value from the Selection Table I get an error that a single value cannot be determined.  Any thoughts?

6 REPLIES 6
v-yuezhe-msft
Employee
Employee

Hi @ChrisDC83,

If there is no relationship between Selection_Table and fact table, Power BI Desktop doesn’t know choose which of the two values in the Selection_Table[Selection]  field to use. You can wrap Selection_Table[Selection] in a FIRSTNONBLANK() function to solve this issue. 

There is a similar blog for your reference.
http://breaking-bi.blogspot.sg/2016/06/power-bipowerpivot-using-slicers-to.html

If the above issue still persists, please post sample data of your tables.

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.


@v-yuezhe-msft wrote:

Hi @ChrisDC83,

If there is no relationship between Selection_Table and fact table, Power BI Desktop doesn’t know choose which of the two values in the Selection_Table[Selection]  field to use. You can wrap Selection_Table[Selection] in a FIRSTNONBLANK() function to solve this issue. 

There is a similar blog for your reference.
http://breaking-bi.blogspot.sg/2016/06/power-bipowerpivot-using-slicers-to.html

If the above issue still persists, please post sample data of your tables.

Thanks,
Lydia Zhang


I use that approach you linked to in the blog frequently for measures, but for a column it doesn't seem to work as easily.

 

Essentially my table looks like this:

 

Fact:

Month | Product Line | Product | Sales Volume | Sales $ | Profit

 

 

So what I'm trying to do is get a variable that swaps between Product Line and Product depending on what the user wants to see.

Hi @ChrisDC83,

What is the problem of your calculated column? Could you please post a snapshot of your expected result?

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Chris,

 

I have the same issue. Please let know if you have found a solution.

 

 

Thanks,

Abhishek.

You dont need a selection table here. You have your calc column for your slicer value, so put that column as the field in your slicer.


@dkay84_PowerBI wrote:
You dont need a selection table here. You have your calc column for your slicer value, so put that column as the field in your slicer.

I'm not so sure I understand.  What would my calculated column have populated in it without knowing if the user wants product line or product name?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.