Filter Fact table based on Dim table

[SivaMani]

Hello All,

I have the following tables,

• Dim_PRD - Applied few filters in Query Editor
• Fact_Finance - Create a relationship between Dim and Fact

The requirement is,

I need to filter the Fact table based products which are available in Dim. For example, I filter 5 products in Dim. I would like to have only those 5 products in Fact.

Is there any option to do this without having a calculated table?

Thanks,

Siva

[v-frfei-msft]

Hi @SivaMani,

I made one sample for your reference.

1. Create a measure as below and filter the fact table by the measure. make the measure is 1.

Measure = IF(MAX('fact'[product]) in VALUES(Dim[product]),1,0)

For more details, please check the pbix as attaced.

Regards,

Frank

[SivaMani · Accepted solution]

@v-frfei-msft, 

Thanks for the response.

It works if we have a relationship between the tables and firing a query to apply the filter explicitly by adding the filter in Report. 

[PattemManohar]

@SivaMani You mean, you want to filter the Fact table data when you use them in the visuals (as per the products available in Dim table). Could you please post a sample data before suggesting you something.

[SivaMani]

Thanks for the reply @PattemManohar,

Yes. You're right. In my case, I'm not using any field in the visual. 

I have a solution that merging to tables. It is costly in my case. So looking for a better way to do this.

I can't post any data here.