Forum Discussion
Trying to control values in a line chart using disconnected slicers
Hi
I have partially achieved what I want to achieve, using two identical disconnected tables as slicers. The following is a page of my report:
So the yellow slicer filters the yellow line, and the blue slicer filters the blue line. This allows me to choose what demographics to compare.
I have achieved this by creating a measure for each entry in the slicer, so the measure for Male would look like this:
Retention Male = CALCULATE([Retention From September],Enrolments[Gender]="M")
If it helps, the Retention From September measure is:
Retention From September = [Completed / Continuing]/[Total Initial Enrolments]
I have 11 of the first measure so far (one for each entry in my slicer - and I need to add more), and then each value in the line chart is one long "nested if" which chooses one of these measures depending what is chosen in the slicer. So:
Retention Slice 1 = if(SELECTEDVALUE('Retention Option 1'[Retention Options 1],"Polar4 Q1") = "Polar4 Q1",[Retention Polar4 Q1],if(SELECTEDVALUE('Retention Option 1'[Retention Options 1]) = "Polar4 Q1&2",[Retention Polar4 Q1&2],if(SELECTEDVALUE('Retention Option 1'[Retention Options 1]) = "Polar4 Q3,4&5",[Retention Polar4 Q34&5],if(SELECTEDVALUE('Retention Option 1'[Retention Options 1]) = "Polar4 Q5",[Retention Polar4 Q5],if(SELECTEDVALUE('Retention Option 1'[Retention Options 1]) = "Disability Not Declared",[Retention Disability Not Declared],if(SELECTEDVALUE('Retention Option 1'[Retention Options 1]) = "Not Disabled",[Retention Non Disabled],if(SELECTEDVALUE('Retention Option 1'[Retention Options 1]) = "Disabled",[Retention Disabled],if(SELECTEDVALUE('Retention Option 1'[Retention Options 1]) = "Male",[Retention Male],if(SELECTEDVALUE('Retention Option 1'[Retention Options 1]) = "Female",[Retention Female],if(SELECTEDVALUE('Retention Option 1'[Retention Options 1]) = "Bachelors",[Retention Bachelors],if(SELECTEDVALUE('Retention Option 1'[Retention Options 1]) = "Foundation",[Retention Foundation],0)))))))))))Retention Slice 2 = if(SELECTEDVALUE('Retention Option 2'[Retentions Options 2],"Polar4 Q1") = "Polar4 Q1",[Retention Polar4 Q1],if(SELECTEDVALUE('Retention Option 2'[Retentions Options 2]) = "Polar4 Q1&2",[Retention Polar4 Q1&2],if(SELECTEDVALUE('Retention Option 2'[Retentions Options 2]) = "Polar4 Q3,4&5",[Retention Polar4 Q34&5],if(SELECTEDVALUE('Retention Option 2'[Retentions Options 2]) = "Polar4 Q5",[Retention Polar4 Q5],if(SELECTEDVALUE('Retention Option 2'[Retentions Options 2]) = "Disabled",[Retention Disabled],if(SELECTEDVALUE('Retention Option 2'[Retentions Options 2]) = "Not Disabled",[Retention Non Disabled],if(SELECTEDVALUE('Retention Option 2'[Retentions Options 2]) = "Disability Not Declared",[Retention Disability Not Declared],if(SELECTEDVALUE('Retention Option 2'[Retentions Options 2]) = "Male",[Retention Male],if(SELECTEDVALUE('Retention Option 2'[Retentions Options 2]) = "Female",[Retention Female],if(SELECTEDVALUE('Retention Option 2'[Retentions Options 2]) = "Bachelors",[Retention Bachelors],if(SELECTEDVALUE('Retention Option 2'[Retentions Options 2]) = "Foundation",[Retention Foundation],0)))))))))))In the line chart this then looks like this:
All is good so far, and this works - I an able to make a single selection on slicer 1 and a single selection on slicer 2, and compare the two on the same chart. However, I have two obstacles to overcome.
- My boss wants to be able to choose more than one selection from each slicer. The long "nested if" measure above only allows for one selection to be made from each slicer. I can change the slicer to allow multiple selections but the multiple selections don't work with the measures above. It only works for single selections. Apart from my disconnected tables, all of my data is in one table, so my first thought was to not use a disconnected table, but use the columns in the table as slicers, but I cannot work out how this would work. It would affect both measures, not just the one. Also, I can only pull one field into a slicer, not multiple fields, so it wouldn't work anyway.
- The second problem is how to show the numbers behind the percentages - as the line chart values come from long nested if statements (see above), those filters don't apply to the other measures that I would want to show (the measures that contain the numbers), so when I apply measures in the tooltips section, they show the numbers unfiltered (i.e. unaffected by the slicer).
I realise that this is a long and complicated post - I've been tearing my hair out all day, and am getting nowhere with it. I'm quite pleased with what I have achieved so far, but I don't see a way forward with what I want to achieve now.
I hope someone can help? Let me know if you need more detail. I don't want to make the file public as it contains sensitive data but I could do more screenshots certainly.
Many Thanks in advance for any help you can give....
Ian
Thanks, v-lili6-msft - Your help has taught me so much about working in Power BI.
The solution, in the end, came from my boss who had a play and worked out it was best done by creating two datasets in Power Query from one, and then having a measure in each dataset that is plotted on the visual. That way you don't need any slicer tables - you slice on the values in each dataset - I've tested and it works.
The link is here to the solution - Final Solution
Thanks again
10 Replies
v-lili6-msftCommunity Support
HI, Anonymous
you could try this way as below:
For example: I use a simple sample to show for you
For all your measure add a if conditional like below:
Measure 1 = IF("A" IN VALUES(Table2[Options]), CALCULATE(SUM(Table1[Index])))Measure 2 = IF("B" IN VALUES(Table2[Options]),CALCULATE(SUM(Table1[Sales])))Measure 3 = IF("C" IN VALUES(Table2[Options]), CALCULATE(SUM(Table1[Sales]),ALL(Table1)))Then drag all these measrue into line Values
Now, when you single selection in table2, it works as before
But when you select more than one value, it will show multiple lines
and here is my sample pbix file, please try it.
Best Regards,
Lin
Hi v-lili6-msft
Thanks for that - I hadn't thought of each measure having the if statement in - it possibly helps simplify things a little. However, it still doesn't do exactly what I want (or what my boss wants). What I want is just two values (lines) on my line chart, and the ability to apply multiple filters to each value (line) on my chart. So the logic would be something like:
If one filter selected then
calculate(measure) using one filter else
if more than one filter selected then
calculate (measure) using however many filters are selected
That's the logic I want to achieve - I'm just not sure if it is possible
- v-lili6-msft
hi, Anonymous
For all these attributes are in different columns, If you could try this way as below:
Step1:
In Edit Queries, Add an index column for data query.
Step2:
Duplicate the data query, and then select all the attributes columns, then Unpivot them
(Before duplicate query, the basic data query must be added an index column)
Step3:
Create the relationship between basic data table and duplicate data table by Index column
(cross filter direction is "Both")
Step4:
Add the attributes columns for each retention slice table
Step5:
Then the measure should be like this
measure = CALCULATE([Retention From September], filter(Duplicate table, 'Duplicate table' [Value] IN Values(retention slice[attributes] ) ) )
Hope this logic could help you.
Best Regards,
Lin