Forum Discussion
Filtering on one item will select all previous items too
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!
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
10 Replies
- amitchandak
Super User
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))
ormeasure =
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))- amitchandak
Anonymous , display this measure with name.
- 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
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
