Work around with line chart and slicer

[tspoon]

Dear all,

I want to create a slicer to filter a line chat viz with drop down selector to this line chart:

No	Activity	Week	Plan	Actual
1	Demolition	0	0.00%	0.00%
2	Demolition	1	0.00%	0.00%
3	Demolition	2	0.22%	0.31%
4	Demolition	3	0.49%	0.62%
5	Demolition	4	0.60%	0.78%
6	Demolition	5	1.42%	1.84%
7	Demolition	6	1.63%	2.63%
8	Demolition	7	3.06%	3.71%
9	Demolition	8	4.92%	6.95%
10	Demolition	9	6.29%	9.58%

Problem is if I choose, for example, Week 4, it will show only the Week 4 data in line chart with dots of plan and actual:

Is it possible if I choose Week 4 from slicer then the line chart show data from Week 1 to Week 4 without holding Control for multiple selection? So, when I select Week 4, it is actually selecting data of Week 1 to Week 4:

Many thanks in advance.

[Ashish_Mathur]

Hi,

I have solved a similar problem in the attached files.

Hope this helps.

[tspoon]

Dear @Ritaf1983 ,

I am aware using range slicer is easier, but it take lots of space in my page. It's quite full of visualization already, that's why i insist  to user drop down slicer.

Thank's for your suggestion. But i consider it as an alternative for now. Hoping there is solution for my case.

[Ritaf1983 · Accepted solution]

Ok @tspoon 
Then, you need to add a slicer to the table that has no relationship with the table of the line chart.

And create flag measure :

Flag =

if(SELECTEDVALUE('Table'[Category]) <= max('Slicer'[Category]),1,0)

This measure can be used as a filter for the line

Result:

Pbix is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[tspoon]

Thank you so much for your help dear @Ritaf1983 !

[tspoon]

Dear @Ritaf1983 ,

Is it possible to keep the scale (x-axis) to the max value for any value that we select in the slicer? 

For example, if we select 6 in the slicer, the line will show value 1-5 but the x-axis still showing the max value (9)

[Ritaf1983]

Hi @tspoon 

Yes, you can create a measure :

Max_X = CALCULATE(MAXX('Table','Table'[Category]),all('Table'[Category]))

And use it for modifying the max X-axis value:

Result:

The updated pbix Is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[tspoon]

Sorry, if i may, need your help again, dear @Ritaf1983 

I modified the table of your "slicer until.pbix", the "Flag" and Max-X measurement :
CategoryPlanActual

1	0%	0.00%
2	15%	14.00%
3	30%	27.00%
4	50%	57.00%
5	75%	68.00%
6	100%	100.00%

Flag =  IF(SELECTEDVALUE('Table'[Category]) <= max('Slicer'[Category]),1,0)

Max_X = CALCULATE(MAXX('Table','Table'[Category]),all('Table'[Category]))

The result worked fine.

But, when I did try to do the same thing with y-axis to make it always at 100%, but it seems not working:

Max_Y = CALCULATE(MAXX('Table','Table'[Plan]),all('Table'[Plan]))

Do you have any suggestion?

ps: how do we attach the pbix file to this message?

[Ritaf1983]

Hi @tspoon 
You can create the measures :

Act = sum('Table'[Actual])

Plan_% = sum('Table'[Plan])

Max_X = CALCULATE(MAXX('Table','Table'[Category]),all('Table'[Category]))

Max_Y = CALCULATE(MAXX(SUMMARIZE('Table','Table'[Category],"plan",[Plan_%]),[plan]),all('Table'[Category]))

Result :

The updated pbix is attached.

According to the attached files, the option is available for superusers only, if you need to share a file, you can upload it to some public drive and share via hyperlink.

more information about this here :
How to Get Your Question Answered Quickly  

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[tspoon]

One last thing...
Is it possible to make the y-axis shows fix lines (from the first to the last value) but the x-axis "move" according the slicer?

[Ritaf1983]

Hi @tspoon 
The easiest way is to use a range slicer instead of a list...

The pbix is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.