Forum Discussion
Trying to slice a visual based off multiple columns
Hello! I'm running into something I've never ran into before and can't seem to solve after some time spent googling and trying things. What I'm trying to do is slice a page by a bunch of columns instead of a single column with the slicer values in it. Since I'm trying to show a trend of the numerical value, I am using a line chart and can't get the values to populate on the Y-Axis.
Here is an example of the data:
So to give an example and an expected output, I am trying to make a slicer that has the slicer values as A, B, C, D, E, and F. If a user chooses to slice on C, the line graph would only display 2, for January, 5 for February, and 6 for March. If a user does not choose to slice on A-F, the line graph would show all the values.
What have I done so far? The closest I've gotten is using Fields option under the New Parameder in the Modeling tab. After choosing my columns, this has made a slicer exactly as I expected, using columns A-F as the value. The problem is, the slicer does not affect the line graph. I have attempted to use both the the A-F columns as well as the new "Consolidated" value from using the New Parameter but the later is a measure and measures, or at least this one, don't typically work in the Y-Axis.
I found a thread that mentioned a very similar case and a solution, but I wasn't able to download the .pbix to see how it worked. https://community.fabric.microsoft.com/t5/Desktop/multiple-Y-axis-line-chart-slice-by-y-axis-fields/m-p/2488853#M885894
Appreciate any help!
Hi Synik_PBI ,
Sorry, as far as I know, the newly generated field parameter is the text type, and the Y-axis in the line chart requires the numeric type, which will show blank when placed in.
You can do this in two ways:
Create a slicer table using Enter data, then use measure+switch():
1. Enter data.
2. Create measure.
Measure = SWITCH( SELECTEDVALUE('Slicer_Table'[Value]), "A",SUM('Table (2)'[A]), "B",SUM('Table (2)'[B]), "C",SUM('Table (2)'[C]), "D",SUM('Table (2)'[D]), "E",SUM('Table (2)'[E]), "F",SUM('Table (2)'[F]))3. Result.
You can also use Unpivot Columns in Power Query to convert ABCDEF columns to a single column, but this will change the format of the entire table.
1. Power Query -- Unpivot Columns .
2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
4 Replies
Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523Hi,
In the Query Editor, select the first 3 columns, right click and select "Unpivot Other Columns". Rename the attribute column to something meaningful. Now create your visual.
Hi Synik_PBI ,
Sorry, as far as I know, the newly generated field parameter is the text type, and the Y-axis in the line chart requires the numeric type, which will show blank when placed in.
You can do this in two ways:
Create a slicer table using Enter data, then use measure+switch():
1. Enter data.
2. Create measure.
Measure = SWITCH( SELECTEDVALUE('Slicer_Table'[Value]), "A",SUM('Table (2)'[A]), "B",SUM('Table (2)'[B]), "C",SUM('Table (2)'[C]), "D",SUM('Table (2)'[D]), "E",SUM('Table (2)'[E]), "F",SUM('Table (2)'[F]))3. Result.
You can also use Unpivot Columns in Power Query to convert ABCDEF columns to a single column, but this will change the format of the entire table.
1. Power Query -- Unpivot Columns .
2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Great solutions! Thanks so much!
I always forget about the power of unpivoting, I went with option #2 and it worked as expected.
