Trying to control values in a line chart using disconnected slicers

[Anonymous]

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.

1. 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.
2. 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

[v-lili6-msft]

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

[Anonymous]

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

[Anonymous]

Hi @v-lili6-msft 

Thanks very much for the help getting there - however, I still have some anomalies that I can't figure out.

I have simplified my report, for now, to just show the one line, while I try to get the multi-selection working. I have also created a second page which is just showing the Retention From September measure with page filters for me to check the accuracy

Unfiltered I get a match between the two, as shown below:

I also get a match if I use a single selection:

However, if I make a multi-selection using two different attributes, it affects the calculation in the first report but doesn't match the check I have done on the second page, just plotting the base measure and using page filters:

This is the measure that I created to plot the line trying to use the slicer - like I say it seems to work and is certainly changing the calculation, but it isn't producing accurate results (according to my test page).

Line 1 = CALCULATE([Retention From September],FILTER(Duplicate,Duplicate[Value] in VALUES(Duplicate[Value])))

I suspect there is something wrong in my measure - like I said I don't want to post the file here as it contains sensitive information, but I have sent you a private message with a link to it in. Any help you can be would be fantastic.

Many Thanks

Ian

[Anonymous]

I have tried changing the measure to the following, but to no avail. It then displays nothing:

Line 1 = CALCULATE([Retention From September],FILTER(Duplicate,Duplicate[Value] in VALUES(Duplicate[Attribute])))

Cheers

Ian

[Anonymous]

Hi @v-lili6-msft 

Just wondering if you'd had any more ideas - I'm at the end of things to try now - so close (thanks to your help, which is much appreciated) - I just cannot get it to produce accurate data.

Thanks again for your help

Kind Regards

Ian

[Anonymous]

OK - I was reluctant to share the pbix file before - I have now cleaned it from all sensitive data and simplified it so it is much more accessible - @v-lili6-msft  has got me so close, I can almost taste it, but I cannot get past the final hurdle.

The link is here, as I cannot attach it - PBIX file

For explanation, there are two pages in the report - The first one is the one I am trying to get working - the second one shows the correct figures, but doesn't have the functionality that I need.

All help gratefully received.

Many Thanks

Ian

[Anonymous]

Hi @v-lili6-msft 

I have created a much simpler file based on fruits and their characteristics and a simple countrows, so I can cross check the actual table and work out what is going on. Much easier to troubleshoot numbers rather than percentages.

The file is here: Fruits PBIX

Again, there are two pages - one to test the solution, and one for control purposes.

I have at least figured out what is going on - I just don't know what to do about it.

So we have the measure - 

Line 1 = CALCULATE(COUNTROWS(Fruits),FILTER(Duplicate,Duplicate[Value] in values(Slicer[Value])))

Because there are many lines in the duplicate table, it is actually adding numbers together rather than further filtering. So in my test page, when I choose Hard and France from the separate slicers, I get the following (which is correct - I have applied the filters directly in the table).

2012 - 2

2013 - 1

2014 - 2

However, when I use the test page, with the one slicer, it is giving me greater numbers

2012 - 12

2013 - 13

2014 - 14

WHat it seems to be doing is giving me is this:

Every row that contains France + Every Row that Contains Hard (Apart from rows that contain France as they have already been counted).

What I want is every row that contains France and Hard - a proper filter.

Does that make sense? I hope there is a solution - this simpler file at least lets us see what is going on in a more straightforward fashion.

Many Thanks

Ian

[Anonymous · Accepted solution]

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

[PBI_Intern_Swap]

Hello,

Hope your are doing good!

I'm working on the same problem. I'm trying to access the file but seems like PBIX file is removed. Could you please share the PBIX file... it will be very helpful.

Thanks