How can my model my diagram in powerbi such that filter propogation works here?

[balaganeshmohan]

I have been stuck on this problem for few days now, it is inherently a modelling issue so I don't think an solution with dax expression exist. I cannot use a measure because I need to filter on report level, I cannot create a column because it does not have filter context.

I want to filter SKU forecast table on 2 levels, one with 'Status' from InOUtstatuses and 'year' from date table. So I need to show one status per respective year. How can I design my model filter direction such that it works in this case? I can only do one or the other filter at a time, but not both.

My sample model table relationship looks like this,

 InOUtstatuses

DateFrom	SKUCode	Status
2-jun-21	A	PREBOOK
2-jun-21	A	PREBOOK
1-sep-22	A	CURRENT
1-sep-22	A	CURRENT
1-sep-22	A	CURRENT
1-nov-20	A	PREPARATION
1-nov-20	A	PREPARATION
1-nov-20	A	PREPARATION
1-jan-22	A	INTRODUCTION
1-jan-22	A	INTRODUCTION
1-jan-22	A	INTRODUCTION
2-jun-21	A	PREBOOK
1-mrt-20	A	DEVELOPMENT
1-mrt-20	A	DEVELOPMENT
1-mrt-20	A	DEVELOPMENT
2-jun-21	B	PREBOOK
2-jun-21	B	PREBOOK
1-sep-22	B	CURRENT
1-sep-22	B	CURRENT
1-sep-22	B	CURRENT
1-nov-20	B	PREPARATION
1-nov-20	B	PREPARATION
1-nov-20	B	PREPARATION
1-jan-22	B	INTRODUCTION
1-jan-22	B	INTRODUCTION
1-jan-22	B	INTRODUCTION
2-jun-21	B	PREBOOK
1-mrt-20	B	DEVELOPMENT
1-mrt-20	B	DEVELOPMENT
1-mrt-20	B	DEVELOPMENT

Date table is standard date table, item_variant is a standar one item per row table.

SKuFOrecast

week number	item	location	sku_forecast	year	SKU Code	Week Start Date	Stock Availability ALL	Available Forecast	Calendar Year
3	A	all	4	2020	A	13-1-2020	1	4	2020
5	B	all	9	2020	B	27-1-2020	1	9	2020
4	C	all	6	2020	C	20-1-2020	1	6	2020

[JorgePinho]

Hello @balaganeshmohan !

Have you tried to change the filter direction to both on the relationship of InOUtstatuses with ItemVariant?

[balaganeshmohan]

It does work, but is it advisable? Because, I would end up using this solution across many report which might end up with unnecessary problems.

EDIT: Been checking the numbers. But I just get repeated numbers for all status in a selected year,

[JorgePinho]

It is hard to say what is the best without more information. Is there a way to connect INOUT to Forecast? Would it make sense to merge INOUT with Item Variant or with Forecast?