Forum Discussion
Cumulative Filter for previous values
Hello folks,
I am newbiew in Power BI, and now I am struggiling to create a formula for filtering.
This would be my data set example:
| Country | Mark | Minutes |
| AA | aa11 | 30 |
| AA | aa22 | 60 |
| BB | bb88 | 120 |
| BB | bb00 | 180 |
| CC | cc99 | 380 |
| DD | dd55 | 480 |
| DD | dd45 | 550 |
| EE | ee98 | 1440 |
| EE | ee33 | 1520 |
Now, I would need to create a filter like this:
IF Minutes < 120, "< 2 hrs",
if minutes < 240, "< 4 hrs",
if minutes < 480, "< 8 hrs",
if minutes < 960, "< 16 hrs",
if minutes < 1440, "< 1 day",
if minutes >= 1440, "> 1 day"
But, if I select filter "< 8 hrs" I would like to include all values for "< 8 hrs", "< 4 hrs" and "< 2 hrs".
Also, if I filter e.g. "< 4 hrs", I would need to include values for "< 4 hrs" and "< 2 hrs", and so on and so forth.
I tried several solutions, like first I created a calculated column:
NumericCategory = SWITCH(
TRUE(),
'Table1'[Minutes] < 120, 1, // "< of 2 hrs"
'Table1'[Minutes] < 240, 2, // "< of 4 hrs"
'Table1'[Minutes] < 480, 3, // "< of 8 hrs"
'Table1'[Minutes] < 960, 4, // "< of 16 hrs"
'Table1'[Minutes] < 1440, 5, // "< of 1 Day"
'Table1'[Minutes] >= 1440, 6
)
Then I created additinal calculated column:
TextCategory =
SWITCH(
[NumericCategory],
1, "< 2 hrs",
2, "< 4 hrs",
3, "< 8 hrs",
4, "< 16 hrs",
5, "< 1 day",
6, "> 1 day"
)
And finally, I created a new measure:
CumulativeFilter =
VAR SelectedTextCategory = SELECTEDVALUE(Table1[TextCategory])
VAR MaxNumericCategory =
SWITCH(
SelectedTextCategory,
"< 2 hrs", 1,
"< 4 hrs", 2,
"< 8 hrs", 3,
"< 16 hrs", 4,
"< 1 day", 5,
"> 1 day", 6
)
RETURN
CALCULATE(
SUM(Table1[Minutes]),
FILTER(
Table1,
Table1[NumericCategory] <= MaxNumericCategory
)
)
And when I use "TextCategory" as a slicer and select e.g. "< 4 hrs" it filtered me only the records that are fitted for this condition. It won't me include the values for "< 2 hrs".
Any idea how to achive this?
Thank you in advance.
exe_binary OK, one, I had an error in the measure:
Selector = VAR __Minutes = MAX('Table'[Minutes]) VAR __Value = MAX('Filter table'[Minute Filter]) VAR __MaxMinutes = SWITCH( __Value, "< 2 hrs", 120, "< 4 hrs", 240, "< 8 hrs", 480, "< 16 hrs", 960, "< 1 day", 1440, 1000000 ) VAR __Result = IF( __Minutes <= __MaxMinutes, 1, 0 ) RETURN __ResultSecond, did you add the measure to the Filters pane and filter for 1? I attached a PBIX file that shows the correct configuration. Below signature.
5 Replies
- exe_binaryFrequent Visitor
Greg_Deckler - I fixed the error, so "Selector" works fine, but I don't know how to fix it to work for "> 1 day".
Finally, solution 🙂
I needed to add additinal row with value "All" in disconnected table and to update a "Selector" like:Selector = VAR __Minutes = MAX('Table1'[Minutes]) VAR __Value = MAX('Filter table'[Minute Filter]) VAR __MaxMinutes = SWITCH( __Value, "< 2 hrs", 120, "< 4 hrs", 240, "< 8 hrs", 480, "< 16 hrs", 960, "< 1 day", 1440, "> 1 day", 1441, 1000000 ) VAR __Result = IF(__MaxMinutes = 1441, IF(__Minutes>__MaxMinutes,1,0) ,IF(__Minutes<=__MaxMinutes,1,0)) RETURN __ResultNow, everything works fine.
Thank you. - Greg_DecklerCommunity Champion
exe_binary Use a disconnected table for your <2, <4, <8 values. You can then create a Complex Selector measure like this:
Selector = VAR __Minutes = MAX('Table'[Minutes]) VAR __Value = MAX('Disconnected Table'[Value]) VAR __MaxMinutes = SWITCH( TRUE(), "<2", 120, "<4", 240, "<8", 480, "<16", 960, "<1 day", 1440, 1000000 ) VAR __Result = IF( __Minutes <= __MaxMinutes, 1, 0 ) RETURN __Result - exe_binaryFrequent Visitor
Greg_Deckler - sorry, maybe I did something wrong but it doesn't work for me.
So, I created a new table with just one column (disconnected table):Then in my "Table1", I created a Selector per your instructions:
Selector = VAR __Minutes = MAX('Table1'[Minutes]) VAR __Value = MAX('Filter table'[Minute Filter]) VAR __MaxMinutes = SWITCH( TRUE(), "< 2 hrs", 120, "< 4 hrs", 240, "< 8 hrs", 480, "< 16 hrs", 960, "< 1 day", 1440, 1000000 ) VAR __Result = IF( __Minutes <= __MaxMinutes, 1, 0 ) RETURN __ResultNow, when I filter the table that I created from columns from Table1, nothing happens.
- Greg_DecklerCommunity Champion
exe_binary OK, one, I had an error in the measure:
Selector = VAR __Minutes = MAX('Table'[Minutes]) VAR __Value = MAX('Filter table'[Minute Filter]) VAR __MaxMinutes = SWITCH( __Value, "< 2 hrs", 120, "< 4 hrs", 240, "< 8 hrs", 480, "< 16 hrs", 960, "< 1 day", 1440, 1000000 ) VAR __Result = IF( __Minutes <= __MaxMinutes, 1, 0 ) RETURN __ResultSecond, did you add the measure to the Filters pane and filter for 1? I attached a PBIX file that shows the correct configuration. Below signature.
- exe_binaryFrequent Visitor
Hey Greg_Deckler - I tested your PBI report and everything works fine except the value that are > 1440. Picture below.
I tried to modify the "Selector" but without success.Also, when I add "Selector" to my table, I get this error: