How to create slicer for multiple fact tables showing only active values in selected period

[Anonymous]

Hi community,

I need your help with creating slicer containing active values only.

I have three fact tables (plan1, plan2 and production) each connected to two dim tables (calendar and items).

Plan1

ItemNr	Date	Quantity
A	10.7.2022	10
B	10.8.2022	5
C	12.8.2022	3
B	5.9.2022	6

Plan2

ItemNr	Date	Quantity
A	1.7.2022	5
A	5.7.2022	5
B	31.7.2022	5
C	5.8.2022	3
B	5.9.2022	6

Production

ItemNr	Date	Quantity
A	30.6.2022	3
A	2.7.2022	4
B	1.8.2022	1
C	1.8.2022	2
B	7.9.2022	5

Items

ItemNr	UoM
A	KG
B	PC
C	M

I also have two slicers, date period from calendar and unit of measure (UoM) from items. What I want here is to filter slicer UoM to show only units active in selected period. Active means all UoM according to items tracked in at least one of the three fact tables in selected period.

In the example above for selected period

1.8.2022-31.8.2022 the slicer should offer PC and M,

1.6.2022-30.6.2022 the slicer should offer KG only.

I would appreciate any help. Thank you.

[johnt75]

You could create a measure like

Show slicer value =
VAR SelectedItems =
    DISTINCT (
        UNION (
            VALUES ( Plan1[Item] ),
            VALUES ( Plan2[Item] ),
            VALUES ( Production[Item] )
        )
    )
VAR CurrentItem =
    SELECTEDVALUE ( Items[Item] )
RETURN
    IF ( CurrentItem IN SelectedItems, 1 )

and add that as a filter to the slicer visual, to only show when the value is 1

[Anonymous]

Unfortunately when I tried to use this solution in my original BI it didn't work. So I tried to find the reason for that. There is a scenario which causes the measure giving wrong result. If you add 1 row to Items table the measure won't work as expected anymore. @johnt75 , please take a look at that as I don't have enough knowledge to fix this.

Items

ItemNr	Uom
A	KG
B	PC
C	M
D	KG

[johnt75 · Accepted solution]

Try the below

Show slicer value = 
VAR SelectedItems =
    DISTINCT (
        UNION (
            VALUES ( Plan1[ItemNr] ),
            VALUES ( Plan2[ItemNr] ),
            VALUES ( Production[ItemNr] )
        )
    )
VAR CurrentItems =
    VALUES ( Items[ItemNr] )
RETURN
    IF ( NOT( ISEMPTY( INTERSECT( CurrentItems, SelectedItems))), 1 )

[Anonymous]

@johnt75 , thank you very much. This is now the solution that works.