Choose Dimension Column For Fact table Records instead of Dimension in Filter tab of the report

[Anonymous]

Hi Team,

I have 2 tables, Fact and Dimension. In Fact ( Id,Sales) there are 3 records , In dimension(Id,Status) i have 5 records. There is one to one relationship between Id and whenever i take Id in the Filter tab it shows all the statuses which are not there in Fact.

I dont want Running to be appear in Filter panel.

Could you please help me on this ?

Thanks,

Shubham

[amitchandak · Accepted solution]

@Anonymous , Please change the relation to 1 to Many from Dimension to Fact and Try

[edhans]

A few things @Anonymous 

1. Don't use a 1:1 relationship. That is just two tables split in two. Merge them in Power Query, or create a 1:Many from your DIM to your FACT table.
2. Relationships are not joins. So if you have Open, Closed, and Running in your FACT table but only Open and Closed in your DIM table, Power BI will show you the Running record. If you want that excluded from the model, you need to do that in Power Query. Filter Relationships do not exclude data based on the relationship like Joins in a database can.
3. If you have Open, Closed, and Running in your DIM table but not your FACT table, Running will appear in the filter panel if you use the field from your DIM table - as you should. Again, if you don't want it, filter it out in Power Query to truly exclude the data from the model.

If you need further help, please post some actual data or a link to the PBIX file (with data embedded in Power Query, not with links to outside sources - use the "Enter Data" feature for this) so we can assist. Links on providing data below.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.