Forum Discussion
Duplicate Values shown in slicer caused by Switch Statement
I've got a calculate column which is doing some State conversions in my data. This column is then used as the slicer on my report. By using either nested IF or Switch, the end result is text is appearing twice in my slicer. Here is a simplified version of my statement:
State = SWITCH( [Description], "Gladstone","QLD", "WR - Brisbane","QLD", "Adelaide", "SA", "Wollongong","NSW", "Nowra","NSW", "Northern Territory","NSW", SWITCH( [ParentId], 21,"NSW", 2,"QLD", "TEST" ) )
"Test" is where i've removed some additional code hoping to narrow down the fault.
Here is what the slicer looks like:
If i was to select a single "QLD", both are shown as selected and the slicer behaves as you would expect if "QLD" was selected. Its almost as if Power BI knows both QLDs are the same value but is mistakely drawing both in its list.
hi Anonymous
I think it somehow has to do with the nested SWITCH inside your SWITCH.
What I would do in your situation is to do this in the Query Editor and create a conditional column. Because it applies the conditions from top to bottom you can easily add change it from your [Description] column to your [ParentId] column.
In doing so it will then have all your required states in one column, so that when loaded into the Power BI data model, the slicer will only see it as a standard column and not as a calculated column.
12 Replies
- GilbertQ
Super User
hi Anonymous
I think it somehow has to do with the nested SWITCH inside your SWITCH.
What I would do in your situation is to do this in the Query Editor and create a conditional column. Because it applies the conditions from top to bottom you can easily add change it from your [Description] column to your [ParentId] column.
In doing so it will then have all your required states in one column, so that when loaded into the Power BI data model, the slicer will only see it as a standard column and not as a calculated column.
I know this thread is marked as solved and pretty much dead, but wanted to revive for some insight.
I had a very similar issue extracting slicer values from a calculated column with a far less complicated IF statement thatn the nested SWITCH used in this example. After refreshing my dashboard one day, the 3 values created from the IF were duplicated in the slicer and duplicated in the filter pane, but in the Table View dropdown filter there were only the 3 values I intentionally created. Makes no sense...
I duplicated the calculated column - literally by Ctrl+A, copy & paste into a new calculated column, and the problem is 100% solved.
I would categorize this as a glitch...Thanks GilbertQ, i might have to resort to that. This is a file i've inherited with some questionable choices. Might have to bite the bullet and do as you have suggested.
Still feels like a Power BI Bug.- GilbertQ
Hi Anonymous
Do you have a sample of the data, are both columns in the same table?
I tried what you suggested above and it is only showing me the single states.
What is interesting is that in your picture NSW is only once. Would leads me to believe that it possibly is something in the dataset.
