cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ChrisDC83 Frequent Visitor
Frequent 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
dkay84_PowerBI New Contributor
New Contributor

Re: Help - Using a Slicer to Change Column Value

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.
Moderator v-yuezhe-msft
Moderator

Re: Help - Using a Slicer to Change Column Value

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.
ChrisDC83 Frequent Visitor
Frequent Visitor

Re: Help - Using a Slicer to Change Column Value


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

ChrisDC83 Frequent Visitor
Frequent Visitor

Re: Help - Using a Slicer to Change Column Value


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

Moderator v-yuezhe-msft
Moderator

Re: Help - Using a Slicer to Change Column Value

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.
Aabhishek Frequent Visitor
Frequent Visitor

Re: Help - Using a Slicer to Change Column Value

Hi Chris,

 

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

 

 

Thanks,

Abhishek.