Forum Discussion
Filtering on a value that is not currently present
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
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
Super UserHi 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:
- Create a reference table in Power BI or import it from your SharePoint list that contains all the possible values (Sample1, Sample2, Sample3, etc.).
- Merge this reference table with your actual data based on the choice column.
- 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
Thanks Ritaf1983 , could you please suggest how this merging is done or any sample reference or something ?
- Ritaf1983
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