Forum Discussion
Filter rows to exclude non-changing values
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,
Frank
Thanks 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