Filtering based on a specific value and any other values

[MohannadSh1996]

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.

[mahoneypat · Accepted solution]

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

[MohannadSh1996]

@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"? 

[mahoneypat]

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

[MohannadSh1996]

@mahoneypat, Thanks for the answers!