Fact table filtered by two dimension tables

I think you may have to refine the requirements. I will rather think as different way, change per your needs. 

There are caveats you need to think.

Say, if you have multiple managers for a given "shop id" for a given year. You cannot have sales total easily. 

Coming back, based on your needs, I see the steps as

1. Sales Fact table ... add a column or doing in the measure.

       Year(Sales Date) & "||" & Shop_ID

2. Similarly you do the same ... add a column or doing in the measure.

      Year & "||" & Shop_ID

3. If you add as a column, then you can do 1..M relationship

       With this you can have a filter or slicer per your nees and then do your visualization per your needs.

I used this analogy - Sales actuals vs Sales budget / target. Where we get sales by each date and by each store/department.... where as we keep target at yearly or monthly level.  This link will explain further more: https://goodly.co.in/actual-vs-budget-calculations-dax-powerbi/

Thanks sevenhills ,

I had similar thoughts - creating artificial key shop_id||year, but I am still wondering if there is smarter solution for this issue.

Anyway, Thanks for your reply.

Hi mw_dev ,

What dax formula I should use to have possibility to filter by manager (appropriate in chosen year) and by dates?

So what's your expected output? To filter Order_No?

I have created a data sample and built relationships for better understanding:

Output:

If it's not your expected output, please provide me with more details about your table and your problems or share me with your pbix file after removing sensitive data.

Refer to:

How to Get Your Question Answered Quickly - Microsoft Power BI Community

How to provide sample data in the Power BI Forum - Microsoft Power BI Community

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

 Thanks Anonymous for your reply.

It would be hard for me to attach pbix file because it is part of large data model, but maybe my explanation will be anough.

In you test data model You have no relation between tables Managers and Sales. When You will try to count orders for certain managers in whole period You will get aggregation for all orders in this period (without grouping by manager).

You will see it more clearly when You add to your Managers table values:

2022/567/ Manager A

2022/678/ Manager C

2021/567/ Manager C