Help - Using a Slicer to Change Column Value

[ChrisDC83]

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?

[v-yuezhe-msft]

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

[ChrisDC83]

---

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

[v-yuezhe-msft]

Hi @ChrisDC83,

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

Thanks,
Lydia Zhang

[Aabhishek]

Hi Chris,

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

Thanks,

Abhishek.

[dkay84_PowerBI]

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.

[ChrisDC83]

---

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