Forum Discussion
Filtering based on a specific value and any other values
Hi,
I have a column with many values and the number of values is changing dynamically day by day.
For example, i have this data:
| Id |
| 6rc1234vda_id |
| 5211031330_id |
| 3054029377_id |
| 9611800901_id |
| 2197866822_id |
| 7950801538_id |
| 4966427798_id |
| 1268016850_id |
| 3261882374_id |
BTW, the first value is showing for all the dates, so i want to have a filter with two values (6rc1234vda_id and "any other value"), like:
Is this applicable and how?
Thanks in advance.
If that first value doesn't change, you could add a Groups column with two buckets (the top one and "other values"). See this link
https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-grouping-and-binning
If it does change, you can do it in the query editor too. Just add an Index column starting from 1, and then add a custom column with this formula
= if [Index] =1 then "First Value" else "Other Values"
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
4 Replies
If that first value doesn't change, you could add a Groups column with two buckets (the top one and "other values"). See this link
https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-grouping-and-binning
If it does change, you can do it in the query editor too. Just add an Index column starting from 1, and then add a custom column with this formula
= if [Index] =1 then "First Value" else "Other Values"
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
mahoneypat, In regard of grouping solution, is it as in the following?
Also what if we got a new value in the future, should it considered as "Other"?That is correct. It is up to you if you want to create a group called "Others" (or whatever you want) or use the default Other group. Any new values would go in the Other if you go with the latter, but you would have to update it if you use the former.
Also, the 2nd approach I mentioned should be pretty robust. If you want to show that first value, you could adapt the M code to show the actual value instead of "First Value" just replace "First Value" with a reference to the column name (e.g., [ColumnName]). I can't see the name in the reply window otherwise I would have just typed that.
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
