Forum Discussion

ChrisDC83's avatar
ChrisDC83
Advocate I
9 years ago

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?

Need Help

6 Replies

  • dkay84_PowerBI's avatar
    dkay84_PowerBI
    Microsoft Employee
    9 years ago
    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's avatar
      ChrisDC83
      Advocate I
      9 years ago
      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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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's avatar
      ChrisDC83
      Advocate I
      9 years ago
      Anonymous 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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Hi ChrisDC83,

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

        Thanks,
        Lydia Zhang