Measure dependent on slicer selection

[Anonymous]

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

9	Tango	6	23.03.2020	128	11.05.2020	0
11	Tango	7	11.05.2020	64	29.06.2020	64
13	Tango	8	11.05.2020	48	29.06.2020	80

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

11

Tango

128

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.

[v-gizhi-msft]

Hi,

Please try to change the one-to-many relationship(single) marked by red circle below to both.

Hope this helps.

Best Regards,

Giotto

[Anonymous]

@v-gizhi-msft 

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,

Could you please share your pbix file by OneDrive for Business?

This can let me help you further.

Best Regards,

Giotto

[Anonymous]

Unfortunately I can't send you the file. Please correct me if I am wrong but I think your idea was to create a measure with the FILTER function which returns the filtered 'Stressing' table and this I could use to check if there are multiple ovens with the same name in this new table?

I tried to implement this

Measure = FILTER(Stressing;RELATED(Calendar[CalendarWeek]))
It does not work though because the Calendar table is not handled as a related table to the Stressing table even though I changed both relationships Stressing <-> Stressing(referenced) and Stressing(referenced) <-> Calendar from single to both.

Maybe it would be possible to filter the Stressing table by using the already implemented Calendar Week Slicer.

@v-gizhi-msft 

[v-gizhi-msft]

Hi,

Any good progress?

Best Regards,

Giotto

[Anonymous]

Sadly I could not figure it out.

[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

[AllisonKennedy]

@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. 

https://sites.google.com/site/allisonkennedycv

[Anonymous]

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.