Filtering on one item will select all previous items too

[Anonymous]

Hi, I have a question relating to filters. How can I setup a filter that when you select one value it will also select all the previous values in the table?

Here is a sample data table..

Desired Data result when selecting a Name:

• If I select E from the filter, I expect the data filtered for 1,2,3,4 & 5 to be shown (everything).
• If I select D, I expect the data shown for 1,2,3 & 4
• If I select C, I expect the data shown for 1,2 & 3.
• If I select B, I expect the data shown for 1 & 2.
• And A would just show 1.

Is there a way to achieve this with a measure or a filter setting?

Many thanks!

[Jihwan_Kim]

Hi, @Anonymous 

Please check the below picture and the sample pbix file's link down below.

I suggest having a separate dim-table like below.

Result =
VAR currentname =
MAX ( Names[Name] )
RETURN
CALCULATE (
SUM ( 'Data'[Data] ),
KEEPFILTERS ( FILTER ( ALL ( 'Data' ), Data[Name] <= currentname ) )
)

https://www.dropbox.com/s/6o3p3g2fmdgjcg1/fruit.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Anonymous]

Hi thanks for the reply.

I'm struggling the replicate this on my own pbix.

"Calculation error in measure 'Data'[Result]: DAX comparison operations do not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values."

When I filter A it should show just A

When I filter B it should show A & B.

When I filter C it should show A,B & C etc. etc.

It might be my error perhaps in explaining.

I've attached my pbix using your measure if you wouldn't mind looking but there is an error I can't figure it out.

[I will have to DM the link to dropbox as the forum keeps stopping me from posting due to 'invalid HTML']

Thanks for your assistance

[Jihwan_Kim]

Hi, @Anonymous 

Please check the link down below.

If you do not want to see 1 as a result, you can insert this measure into the visual filters and select the option to show only 1.

You cannot sum alphabets.

In my previous pbix file, the data column was numbers. So I chose the sum function.

If you change the whole sample, you need to apply the proper function according to each column's data type.

And check the data model pane. In my last sample pbix file, the two tables were not connected. Please do not connect the two tables, in this case. If you do not check this after every time you create new model or new tables, in most cases, the relationship is automatically created, unless you change a setting.

https://www.dropbox.com/s/cn7wl9uc28f6i1t/fruit2.pbix?dl=0 

Result =
VAR currentselect =
MAX ( 'Name'[Name] )
VAR currentnameorless =
SUMMARIZE (
FILTER ( ALL ( 'Name' ), 'Name'[Name] <= currentselect ),
'Name'[Name]
)
RETURN
IF ( SELECTEDVALUE ( Data[Name] ) IN currentnameorless, 1, BLANK () )

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Anonymous]

Hi thanks very much for you help and your patience, it is really appreciated.

This is very nearly what I need, it does behave correctly but there needs to be a slight tweak for my use case that I'm not sure how to apply.

This measure works under the assumption that the names are all in alphbetical order and shows equal to or less alphabetical values.

What I need is for the values shown to be based on the lower (or equal to) ref/index number order, not the alpabetical order of the values in the filter.

For example:

The Filter would still have A,B,C,D,E as values as before, however the values shown would be as follows:

Select A: Show A,B

Select B: Show B

Select C: A,B,C,D,E

Select 😧 A,B,E,D

Select E: A,B,E

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

Thank you for your feedback.

In that case, you can have one more column in the Name Table, like below.

Please check the relationship every time you create new tables ( this case: NO relationship), if your setting is auto-relationship-create.

I changed a measure and please check in the sample pbix file link down below.

https://www.dropbox.com/s/6o3p3g2fmdgjcg1/fruit.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Anonymous]

Having the same problem with DMs - the forum says a simple link is invalid HTML??? No it has stopped me posting as I'm flooding? Very frustrating

[Anonymous]

Link to drop box pbix

[amitchandak]

@Anonymous , If a, b, c,d,e are incremental value, means that can work like numbers.

You need to use an independent table of name for that , say name

measure =

var _1 = sleectedvalue(name[name])

var _2 = maxx(filter(Table, Table[Name] =_1 ), table[Data]) // or use index column in place of data

return

calculate(sum(Table[Date]), filter(Table, Table[Date] <=_2))

or

measure =

var _1 = sleectedvalue(name[name])

var _2 = maxx(filter(Table, Table[Name] =_1 ), table[index]) // or use index column in place of data

return

calculate(sum(Table[Date]), filter(Table, Table[index] <=_2))

or

measure =

var _1 = sleectedvalue(name[name])
return
calculate(sum(Table[Date]), filter(Table, Table[Name] <=_1))

[Anonymous]

Thanks for the quick reply.

Where would I apply the measure to?

[amitchandak]

@Anonymous , display this measure with name.