Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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 )
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
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 |
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 )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |