Forum Discussion
Measure dependent on slicer selection
Hi,
in a scenario where the pieces of a product are being stressed in an oven over time I want to calculate the remaining capacity of the ovens during a selected calendar week.
My tables are as follows
Stressing
ID Oven ID Product Start Date Pieces End Date Remaining Capacity
|
I also have made a calculated column 'Remaining Capacity' = RELATED(Oven[Capacity]) - Stressing[Pieces]. A first attempt at solving my problem.
Oven
ID Oven Capacity
|
I hope the Headers are displayed properly.
A product and calendar table which have some more info which is not relevant right now.
The referenced Stressing table is for expanding the dates between start date and end date which I use for a slicer in which I can choose a single calendar week to filter by.
The relationships
Relationships
What I want now is probably a measure which calculates the remaining capacity of the ovens during the selected calendar week which I can then use in a visual. I already made it so that the visual shows the ovens in use during the selected calendar week. The number you can see is the sum of all the pieces that are being stressed in this oven but not limited to this calendar week but of all time. I'd like to replace this aggregation of pieces by the remaining capacity of the ovens.
Using the data from the aforementioned tables the calculation should be something like this
Remaining capacity = 128(Capacity of Oven Tango) - 64(Pieces of Stress with ID 11) - 48(Pieces of Stress with ID 13)
A way to do it could be a measure that is dependent on the slicer selection which checks if there are multiple stresses in the same oven during the selected week and then calculates the remaining capacity.
Or maybe a calculated column in the stressing table with some IF conditions to check if the Oven ID is the same and the date as well and then calculate the remaining capacity.
Any help is greatly appreciated.
9 Replies
- AllisonKennedy
Community Champion
Anonymous It sounds like you're very close to solving this yourself, but I'm curious what the Stressing (Reference) table is doing and where that Date column comes from? With the way your relationships are pointing and that intermediary table, that may be one of the roadblocks to not being able to get the capacity by date.
Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos.
I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query.
Hi AllisonKennedy ,
I made the Stressing(referenced) table to expand my dates between the 'Start Date' and 'End Date' of my Stressing table so I could implement a slicer in which I can select a single calendar week. The idea was from another user from this community he sent me this video: https://youtu.be/7kbNHz6SZHY
The calendar table I have is the ultmate calendar table from Avi Singh. In there the date column contains every day from the year 2020 and lots of more information like the Calendar week this day is in and so on.
- v-gizhi-msft
Community Support
Hi,
Please try to change the one-to-many relationship(single) marked by red circle below to both.
Hope this helps.
Best Regards,
Giotto
I changed the relationship like you told me to but i don't see how this helps me. If you could elaborate on how this is beneficial to solving my problem I would be very grateful.
I am struggling with the DAX syntax for the measure.
- v-gizhi-msft
Hi,
According to your description, i think you want to filter 'Stressing' table by selecting week in 'Calendar' table.
If so, you should change the filter direction from 'Calendar' to 'Stressing', this is why i advise changing the relationship direction.
Best Regards,
Giotto
