Help with ALLEXCEPT NOT WORKING

[olmie_fresh]

Hello, I have a table with table (list) of Maintenance Work Orders - one work order per row, where there is a "Open Date" and "CLosed Date" column to denote timestamp of each WO (if not closed, is "Closed Date" null).

I created a special calendar table 'Date - Open and Closed' (in addition to two I have for Open Date and Close Date) that I reference for calculations that I can then put on the SAME visual (to show opend AND closed count by month).

Problem is, I want my visual to adjust for page filter (namely, "Order Type"), so I included ALLEXCEPT syntax as I expected to work. However, the visual including these formulas DOES NOT respond to the filter. I can't even hard-code it into the syntax of the formulas, and I can't figure out why, even researching others' issues on ALLEXCEPT issues.

I appreciate any help.

Orders Closed =

VAR _a =

    COUNTROWS (

        FILTER (

            ALLEXCEPT ( 'Maintenance Work Orders', 'Maintenance Work Orders'[Order Type] ),

            MONTH ( 'Maintenance Work Orders'[Closed Date] )

                = MONTH ( MAX ( 'Date - Open and Closed'[Date] ) )

                && ISBLANK ( 'Maintenance Work Orders'[Closed Date] ) = FALSE

        )

    )

RETURN

    IF ( _a = BLANK (), 0, _a )

Orders Opened =

VAR _a =

    COUNTROWS (

        FILTER (

            ALLEXCEPT ( 'Maintenance Work Orders', 'Maintenance Work Orders'[Order Type] ),

            MONTH ( 'Maintenance Work Orders'[Open Date] )

                = MONTH ( MAX ( 'Date - Open and Closed'[Date] ) )

        )

    )

RETURN

    IF ( _a = BLANK (), 0, _a )

[Anonymous]

Hi @olmie_fresh,

Have you solved your problem?

If you have any question, please feel free to contact me.

If my reply could help you solve your problem, please consider marking it as the solution, or, you could post the solution that solves your issue.

Thanks for your cooperation.

Best Regards,
Qi
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[olmie_fresh]

To answer your questions: I DO NOT have active links from  'Date - Open and Closed' table to both 'Open Date' and 'Closed Date' columns. I actually create columns to link (duplicated column and dropped time, so

Open - ListDate' and 'Closed - ListDate' match up (and I actually link them to separate Calendar tables for other purposes). When I attempt to activate relationships from 'Maintenance Work Orders'[Open - List date] many to one 'Date - Open and Closed'[Date] AND 'Maintenance Work Orders'[Closed - List date] many to one  'Date - Open and Closed'[Date], I get "There are Ambiguous Paths" error when I attempt to link the SECOND table (in either order) - presumably something to do with the fact that some 'Closed' dates are NULL? And my 'Date - Open and Closed' table was a table generate by DAX caculation (list of dates from first "Open" to today, then added [MonthDate] column that is first of the month date for each row), unlike other Calendar tables I use more actively.

Separately, when I modify the metrics' formulas (both [Orders Opened] and [Orders Closed]) I get the error message below (on visual using these two metrics: "...Error 2100, usually occurs when DistinctCount Measure computed for group-by columns which are indirectly related to the DistinctCount column..."); note that my visual is a simple Clustered Column chart with 'Date - Open and Closed'[MonthDate] on the x-axis and "Orders Opened" and "Orders Closed" on the y-axis.

 Admittedly, I'm relatively new to DAX; mostly SQL background, so I understand data structure concepts. DAX still hard to understand as I'm putting in very clear parameters for a function I understand, yet it doesn't work. Any additional advice you have would be appreciated.

[Anonymous]

Hi @olmie_fresh,

Could you please ensure that 'Date - Open and Closed' table has active relationships with both Open Date and Closed Date in Maintenance Work Orders?

BTW, I guess the MONTH() maybe the issue as well, I suggest you use EOMONTH() or separate year and month as below.

Please try to use CALCULATE() with explicit filters to respect page-level context:

Orders Closed = 
VAR _SelectedDate = MAX('Date - Open and Closed'[Date])  // Get the date from the visual
VAR _StartDate = DATE(YEAR(_SelectedDate), MONTH(_SelectedDate), 1)  // First day of the selected month
VAR _EndDate = DATE(YEAR(_SelectedDate), MONTH(_SelectedDate) + 1, 1) - 1  // Last day of the selected month
RETURN
CALCULATE(
    COUNTROWS('Maintenance Work Orders'),
    'Maintenance Work Orders'[Closed Date] >= _StartDate,
    'Maintenance Work Orders'[Closed Date] <= _EndDate,
    NOT(ISBLANK('Maintenance Work Orders'[Closed Date]))
)

 Here is another measure for the opened orders:

Orders Opened = 
VAR _SelectedDate = MAX('Date - Open and Closed'[Date])  // Get the date from the visual
VAR _StartDate = DATE(YEAR(_SelectedDate), MONTH(_SelectedDate), 1)  // First day of the selected month
VAR _EndDate = DATE(YEAR(_SelectedDate), MONTH(_SelectedDate) + 1, 1) - 1  // Last day of the selected month
RETURN
CALCULATE(
    COUNTROWS('Maintenance Work Orders'),
    'Maintenance Work Orders'[Open Date] >= _StartDate,
    'Maintenance Work Orders'[Open Date] <= _EndDate
)

Best Regards,
Qi
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!