cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TomBLG
Helper I
Helper I

How to use 2 slicers on the same table to populate yaxis and yaxis2

Hello everyone,

 

I have 2 slicers with all the field names in my table (unpivotted the data to be able to make a slicer for this). The idea is for one slicer to populate the y-axis and the other one populate y-axis2.

 

TomBLG_0-1636972100694.png

 

Here is the file with dummy data that replicates my issue.

 

For context, I have 3 tables for the same data, Hourly, Daily and Weekly. The real data has thousands of columns with many of them being complex formulas built in SQL, so this is required to prevent using Hourly average values for those performance formulas -- however that part is all working fine, and replicated in the Dummy file too.

 

Problem: Currently, I'm able to select anything on the 1st slicer and fill y-axis but this makes it so once I select anything in the 2nd slicer the graph goes blank (For instance, Input1+Output1 on yaxis and Performance1 on yaxis-2). I believe I'm missing something in my measures to ensure it looks up data despite the 1st slicer selection. I've tried duplicating the Attribute column (Field names) and use the copy on the 2nd slicer, tried playing around with the interaction between the slicers and tried including an ALL('Hourly') filter in my measure to ignore the slicer1 selected data but it didn't do what I wanted.

 

Bad workaround: Currently I have this working by creating a copy of my 3 tables, Hourly, Daily and Weekly and using the copy on the 2nd slicer which effectively doubles my file's size. As I need to create dozens of similar files refresh them frequently on a schedule I'm hoping I can get this working without copying all my data.

 

If there's anything else I can provide please let me know.

1 ACCEPTED SOLUTION
TomBLG
Helper I
Helper I

Hello,

 

I finally managed to resolve the issue. I'm now able to select any field from the left slicer into y-axis1 and from the right slicer into y-axis2 as a stacked line.

 

TomBLG_0-1637075690167.png

 

After having created 2 tables (TagList & TargetList) with the data's column names for the slicers I had to create a Many-to-Many relationship with a single filter going from the data to these new tables.

 

Thank you for taking the time, pbix file here if you'd like to look into it, marking as solution.

View solution in original post

3 REPLIES 3
TomBLG
Helper I
Helper I

Hello,

 

I finally managed to resolve the issue. I'm now able to select any field from the left slicer into y-axis1 and from the right slicer into y-axis2 as a stacked line.

 

TomBLG_0-1637075690167.png

 

After having created 2 tables (TagList & TargetList) with the data's column names for the slicers I had to create a Many-to-Many relationship with a single filter going from the data to these new tables.

 

Thank you for taking the time, pbix file here if you'd like to look into it, marking as solution.

TomBLG
Helper I
Helper I

I have managed to populate the graph with multiple tags. I was using 'Unpivot Table'[Attributes] rather than the measure I used in the slicer for my Column Series field, however there's 2 remaining issues if anyone is able to offer insight on that.

 

TomBLG_0-1636996028955.png

 

Once more than one option is selected in the slicer, box visualisations will read (blank). Additionally once more than one Target is selected, the y-axis2 line tied to Targets disappears. I suspect this is due to it trying to sum up the values up for the Target. I believe the y-axis1 values display since they're in a stacked bar format and no summing is done.

 

Latest dummy pbix file

 

Thank you for taking the time reading this.

 

TomBLG
Helper I
Helper I

I followed https://community.powerbi.com/t5/Desktop/Two-slicer-on-same-column/m-p/516318 and now I'm very close to what I need.

 

I made 2 calc tables named as instructed:

 

TagFilter1 = DISTINCT(VALUES(hourly[Attribute]))
with measure TagFilter1 Value = SELECTEDVALUE(TagFilter1[Attribute])

 

TagFilter2 = DISTINCT(VALUES(hourly[Attribute]))
with measure TagFilter2 Value = SELECTEDVALUE(TagFilter2[Attribute])
 
I then adapted the measure I use in the graph from:
 
SUM Tags =
SWITCH( TRUE(),
    VALUES('Control Table'[Periodicity]) = "Hourly", CALCULATE(SUM('hourly'[Value])),
    VALUES('Control Table'[Periodicity]) = "Daily", CALCULATE(SUM('daily'[Value])),
    VALUES('Control Table'[Periodicity]) = "Weekly", CALCULATE(SUM('weekly'[Value])),
    BLANK())
 
to:
SUM Tags =
SWITCH( TRUE(),
    VALUES('Control Table'[Periodicity]) = "Hourly", SUMX(FILTER(hourly,hourly[Attribute]=TagFilter1[TagFilter1 Value]),hourly[Value]),
    VALUES('Control Table'[Periodicity]) = "Daily", SUMX(FILTER(daily,daily[Attribute]=TagFilter1[TagFilter1 Value]),daily[Value]),
    VALUES('Control Table'[Periodicity]) = "Weekly", SUMX(FILTER(weekly,weekly[Attribute]=TagFilter1[TagFilter1 Value]),weekly[Value]),
    BLANK())
 
I can now select anything from each slicer and it populates the graph perfectly, however the moment I select multiple tags in the slicer it goes blank. Attached the new file:
 
Single selection:
TomBLG_0-1636976383823.png

 

Multiple selection:

TomBLG_1-1636976426862.png

 

New file here.

 
 

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!