filtering of two fact tables

[Serhii_Andr]

Hello, dear collegues

I got stucked with such problem:

I have a data-model with three simple dimension tables:

Areas { [Area] }

Products { [Product] }

Managers { [Manager], [Area & Product] }

And two simple fact tables:

Sales { [Area], [Product], [SalesFact] }

Plans { [Area], [Product], [SalesPlan] }

As you can see from the Managers table, [Manager] - is the combination of Area and Product (only one manager stands for one unique pair of area-product)

And I want

1) to use slicer on Manager [Manager], pick some manager, and get both of my fact tables filtered on products and areas which belong to that manager

2) another challange for me - when i have successfully picked manager and got filtered both fact tables, then I want to create the matrix and put Products[Product] or Areas[Area] in row area and Sales[SalesFact], Plans[SalesPlan] in values and get the correct results on it

How can i handle this? Thank you in advance

[v-deddai1-msft · Accepted solution]

Hi @Serhii_Andr ，

You can create area&product column in both sales and plans table and create many to one relationship between them and manager table：

 

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

Best Regards,

Dedmon Dai

[amitchandak]

@Serhii_Andr , Merge Area, product, and Manager unless they are needed separately.

Create a concatenated key

Key = [product] & "-" & [Area] in (manager(combined),  sales and plan)

Join these there and analyze with help from Manager

https://radacad.com/append-vs-merge-in-power-bi-and-power-query