Filtering on a value that is not currently present

[Garry030424]

I have a sharepoint List.

Amongst various columns there is choice column with choices like Sample1, Sample2, Sample3, Sample4, Sample5 etc

Now in actual data not all these choices are present.

I need to create view by considering those choices as well which are not present currently but could be present in future.

Example: Current data has sample2 and sample3 in actual data however sample1 is not present. In future someone would add data with Sample1. Now my power bi views should consider such cases as well

[Ritaf1983]

Hi @Garry030424 

It seems like you're asking about ensuring that all possible choices from the SharePoint list (such as Sample1, Sample2, etc.) are accounted for in your Power BI report, even if not all of them are currently present in the actual data.

If I understood correctly and you’re dealing with a multi-choice column where multiple values can appear in a single row, you can easily handle this by using the "Split to Rows" function in Power Query. This will split the multiple choices into separate rows, making it easier to work with those values in your report.

However, if your question is about ensuring that choices (like Sample1) are displayed in the report, even if they are not yet present in the actual data, a good approach would be to create a separate reference table that lists all possible choices from the SharePoint column. You can then merge this reference table with your main dataset, ensuring all choices appear in your report, even if they don’t yet have associated data.

If this doesn’t address the issue, could you provide a bit more detail or an example of the scenario you're dealing with? That way, I can offer more specific guidance.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[Garry030424]

no... so the choice column has certain predefined (Only 1 Selection, no multiple selections) values like sample1, sample2, sample3, sample4, etc

Now in the actual data for this partiuclar column we might only see sample2 or sample3 across data 

In future lets say the actual data also has sample1 then our report should be accomodative of that.

Currently in the power BI filters i just see sample2 and sample3 because only those are entered

[Ritaf1983]

Hi @Garry030424 

Thank you for the clarification! Since the choice column has predefined values and you're only seeing the options (e.g., Sample2, Sample3) that currently exist in the actual data, but you want to accommodate all possible future values (e.g., Sample1) in the report even if they haven't been entered yet, here's the approach you can take:

Solution:

To ensure all predefined values (like Sample1, Sample2, Sample3, etc.) appear in your Power BI report filters, even if not all are currently used in the data, you can create a reference table that lists all the predefined choices.

Here’s how to implement this:

1. Create a reference table in Power BI or import it from your SharePoint list that contains all the possible values (Sample1, Sample2, Sample3, etc.).
2. Merge this reference table with your actual data based on the choice column.
3. Use this merged table for your filters and visuals, ensuring that all choices are displayed, even if some don't have any associated data yet.

This way, Power BI will always display all the options from the reference table, not just the ones currently present in the data.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[Garry030424]

Thanks @Ritaf1983 , could you please suggest how this merging is done or any sample reference or something ?

[Ritaf1983 · Accepted solution]

Hi @Garry030424 
This is a standard merge tables functionality in PQ :

https://learn.microsoft.com/en-us/power-query/merge-queries-overview

Or even just creating a relationship between the table of "transactions" with the reference table that can be used as a filtering table:

https://learn.microsoft.com/en-us/power-bi/guidance/star-schema

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly