Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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?
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
@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
Hi Chris,
I have the same issue. Please let know if you have found a solution.
Thanks,
Abhishek.
@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?
User | Count |
---|---|
84 | |
80 | |
72 | |
71 | |
55 |
User | Count |
---|---|
108 | |
106 | |
93 | |
85 | |
67 |