Forum Discussion

AppleMan's avatar
AppleMan
Helper III
1 year ago
Solved

Algorithm Help

Hi,   I have an interesting question I need help figuring out.  I will start with the overall idea of what im doing. I am taking data from an ERP system that says what days machines can run and ho...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi AppleMan,

     

    Thank you for reaching out to Microsoft Fabric Community Forum.

     

    I used a Switch statement to ensure the context is applied with the condition. Can you try this and let me know

    VAR _UnavailableDaysRemaining =
    CALCULATE(
        COUNTROWS('Date Table'),
        FILTER(
            'Date Table',
            'Date Table'[Date] >= _StartDateRemaining
&& 'Date Table'[Date] <= _EndDate
        ),
        FILTER(
            'Date Table',
            VAR _Weekday = WEEKDAY('Date Table'[Date])
            VAR _IsUnavailable = 
                SWITCH(
                    _Weekday,
                    1, SUMX(VALUES(uv_ProductionCalendar), uv_ProductionCalendar[WorkWeek1]) = 0,
                    2, SUMX(VALUES(uv_ProductionCalendar), uv_ProductionCalendar[WorkWeek2]) = 0,
                    3, SUMX(VALUES(uv_ProductionCalendar), uv_ProductionCalendar[WorkWeek3]) = 0,
                    4, SUMX(VALUES(uv_ProductionCalendar), uv_ProductionCalendar[WorkWeek4]) = 0,
                    5, SUMX(VALUES(uv_ProductionCalendar), uv_ProductionCalendar[WorkWeek5]) = 0,
                    6, SUMX(VALUES(uv_ProductionCalendar), uv_ProductionCalendar[WorkWeek6]) = 0,
                    7, SUMX(VALUES(uv_ProductionCalendar), uv_ProductionCalendar[WorkWeek7]) = 0,
                    FALSE()
                )
            RETURN _IsUnavailable
        )
    )

     

    If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!


    Regards,
    Vinay Pabbu

AppleMan's avatar
AppleMan
Helper III
1 year ago

This code is close to accomplishing what I would like (which as of Monday would be returning 1 for the last unavailable day being Saturday), however it is returning 2. The line causing it to return an extra value is for weekday 3, which makes no sense looking at the data.

 

VAR _UnavailableDaysRemaining =
    CALCULATE(
        COUNTROWS('Date Table'),
        FILTER(
            'Date Table',
            'Date Table'[Date] >= _StartDateRemaining
                && 'Date Table'[Date] <= _EndDate
        ),
        FILTER(
            'Date Table',
            SWITCH(
                WEEKDAY('Date Table'[Date]),
                1, CALCULATE(SUM(uv_ProductionCalendar[WorkWeek1])) = 0,
                2, CALCULATE(SUM(uv_ProductionCalendar[WorkWeek2])) = 0,
                3, CALCULATE(SUM(uv_ProductionCalendar[WorkWeek3])) = 0,
                4, CALCULATE(SUM(uv_ProductionCalendar[WorkWeek4])) = 0,
                5, CALCULATE(SUM(uv_ProductionCalendar[WorkWeek5])) = 0,
                6, CALCULATE(SUM(uv_ProductionCalendar[WorkWeek6])) = 0,
                7, CALCULATE(SUM(uv_ProductionCalendar[WorkWeek7])) = 0,
                FALSE()
            )
        )
    )
Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi AppleMan,

 

Thank you for reaching out to Microsoft Fabric Community Forum.

 

I used a Switch statement to ensure the context is applied with the condition. Can you try this and let me know

VAR _UnavailableDaysRemaining =
    CALCULATE(
        COUNTROWS('Date Table'),
        FILTER(
            'Date Table',
            'Date Table'[Date] >= _StartDateRemaining
&& 'Date Table'[Date] <= _EndDate
        ),
        FILTER(
            'Date Table',
            VAR _Weekday = WEEKDAY('Date Table'[Date])
            VAR _IsUnavailable = 
                SWITCH(
                    _Weekday,
                    1, SUMX(VALUES(uv_ProductionCalendar), uv_ProductionCalendar[WorkWeek1]) = 0,
                    2, SUMX(VALUES(uv_ProductionCalendar), uv_ProductionCalendar[WorkWeek2]) = 0,
                    3, SUMX(VALUES(uv_ProductionCalendar), uv_ProductionCalendar[WorkWeek3]) = 0,
                    4, SUMX(VALUES(uv_ProductionCalendar), uv_ProductionCalendar[WorkWeek4]) = 0,
                    5, SUMX(VALUES(uv_ProductionCalendar), uv_ProductionCalendar[WorkWeek5]) = 0,
                    6, SUMX(VALUES(uv_ProductionCalendar), uv_ProductionCalendar[WorkWeek6]) = 0,
                    7, SUMX(VALUES(uv_ProductionCalendar), uv_ProductionCalendar[WorkWeek7]) = 0,
                    FALSE()
                )
            RETURN _IsUnavailable
        )
    )

 

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!


Regards,
Vinay Pabbu