Forum Discussion
Filter rows to exclude non-changing values
I would like to filter results of a table based on whether the values have changed across columns.
| Period 1 | Period 2 | Period 3 | |
| A | 1 | 1 | 1 |
| B | 1 | 2 | 2 |
| C | 1 | 1 | |
| D | 1 | ||
| E | 1 | 1 | 2 |
In this example, row A would be excluded, as it's value is 1 for all 3 periods.
It is important, that going from 1 to blank (as in Row C) should be considered a change.
Thanks for any suggestions!
Scott
2 Replies
Hi scottbmartin,
Based on my test, we can achieve the goal by the following methods.
1. Add a custom column in query editor. Filter the table as below, close and apply.
custom = if [Period 1] = [Period 2] and [Period 2]=[Period 3] then 0 else 1
2. Add a calculated column to filter the table.
Column = IF(AND(Table1[Period 1]=Table1[Period 2],Table1[Period 2]=Table1[Period 3]),0,1)
For more details, please check the pbix as attached.
Regards,
FrankThanks Frank.
I failed to articulate the underlying data strucuture. The "Table" that I listed in the original post is an example of a PowerBI Matrix visualization. The underlying "Table" would look something like this:
Item Period Value A 1 1 A 2 1 A 3 1 B 1 1 B 2 2 B 3 2 C 2 1 C 3 1 D 1 1 E 1 1 E 2 1 E 3 2 Do you think your approach be applied to this structure?
Thanks, and sorry for not being more clear in my original post.
Ideally, I'd like to be able to have the filter criteria respond to the current filter context. For instance, to allow the user to not only compare the last three periods, but also just the last two, or perhaps 4.
My first thought, was to consider using the STDEVX.P function. If the row's standard deviation is Zero, then there must be no change in value, and thus should be filtered out.
Thanks,
Scott
