topic How to use 2 slicers on the same table to populate yaxis and yaxis2 in DAX Commands and Tips

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

TomBLG — Mon, 15 Nov 2021 10:30:08 GMT

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.

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.

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

TomBLG — Mon, 15 Nov 2021 11:43:58 GMT

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:

Multiple selection:

New file here.

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

TomBLG — Mon, 15 Nov 2021 17:08:54 GMT

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.

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.

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

TomBLG — Tue, 16 Nov 2021 15:21:12 GMT

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.

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.