Filter data by 2 different filters (multiple relationships)

[Anonymous]

Hello, I have a problem in PBI, I will try to explain it.
I have this table structure:

I have one main table - ProductionTable. I have 2 more tables with data for cosumtion and ordered quantities. To be able to make relationships and filter by ContractNo and Product number, so I made a two dim tables with unique values. The idea is to filter by ContractNo and to get filtered data by contract from the 3 tables. I have tried a lot of relationships, but when I try to make 2 relationships between these 3 tables, PBI do not allows me.

At the moment my report works only if I put Slicer for ContractNo from the main table, not from the dim table, and the data in last column OrderedQty is not filtered by the ContractNo and shows summarized for all contracts for this specific product.

Thanks!

[amitchandak · Accepted solution]

@Anonymous , refer if treatas can help

https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous · Accepted solution]

I have watched the video in the article, downloaded the example. pbix files and I think I did it properly.

Thnaks @amitchandak  for the link (https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/ and YouTube video: https://www.youtube.com/watch?v=6WU7Ze32Q3w&feature=emb_title&ab_channel=SQLBI )

I made a measure and my final formula looks like this:

ReaminingQTYbyPU = CALCULATE(
SUM(PurchasingTable[RemainingQTY]),
TREATAS(
SUMMARIZE( MainTable , MainTable[ContractNo]),
PurchasingTable[ContractNo])
)

[v-rzhou-msft]

Hi @Anonymous 

From you screenshot, I found that there will be loops in your relationship.

Please read this blog for more details: Adjusting Cross filter direction for a complex set of tables and relationships

Due to I don't know you data model, I think if you want to get the result which will be filtered by 2 different filters, you can try to build a measure by dax.

You may refer to this blog for more details about how to build a measure: Create Measure using a different filter for two columns

If this reply still couldn't help you solve your problem, please share a sample with me by your Onedrive for Business.

Best Regards,

Rico Zhou

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

[amitchandak · Accepted solution]

@Anonymous , refer if treatas can help

https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

Hello @amitchandak Here is a screenshot of the relationship ana data model:

If you tell me how to delete or replace sencitive data I can attach a .pbix file, but now it is full with company data. The other mtethod is to send me your email and I can send .pbix to you.