If some filter is enabled dax not calculating

[henkka]

Hi,

I'm tryin to come up with measure to calculate employee count by Start date and end date slicer and if no slicer values selected then fixed start and end date.

Dates  VARiables are functionin correctly if there is filter enabled or not so its about the switch part but I don't understand why. only when no filters are enabled the calculation is working.

VAR _ThisYear = YEAR ( TODAY() )

VAR _firstday = EOMONTH(TODAY(), -2)+1

VAR _lastday = EOMONTH(TODAY(), -1)

VAR _SLstartyear = IF ( ISFILTERED ( StartDatesDim[Year] ) , SELECTEDVALUE ( StartDatesDim[Year] ) ,  _ThisYear )

VAR _startmonth = IF ( ISFILTERED ( StartDatesDim[Month Name Short] ) , MIN ( StartDatesDim[Month] ) , 1 )

VAR SLstartdate = IF ( ISFILTERED ( StartDatesDim ) , DATE ( _SLstartyear  , _startmonth , 1 ) , _firstday)

VAR _SLendyear = IF ( ISFILTERED ( Enddatesdim[Year] ) , SELECTEDVALUE ( Enddatesdim[Year] ) ,  _ThisYear )

VAR _endmonth = IF ( ISFILTERED ( Enddatesdim[Month Name Short] ) , MIN ( Enddatesdim[Month] ) , 1 )

VAR SLenddate = IF ( ISFILTERED ( Enddatesdim ) , DATE ( _SLendyear  , _endmonth , 30  ) , _lastday )

VAR _modefilter = FILTER ( 'Newest agreement by employee' , 'Newest agreement by employee'[Work time mode] = "kokoaikainen")

VAR Result =

SWITCH ( TRUE() ,

        ISFILTERED ( StartDatesDim) = TRUE() && ISFILTERED(Enddatesdim) = TRUE() ,

            CALCULATE (

                    COUNT ('Newest agreement by employee'[Workagreement.id] )  ,

                    StartDatesDim[Start date] <= SLstartdate ,

                    Enddatesdim[End date] >= SLenddate || Enddatesdim[End date] = BLANK() ,

                    _modefilter

                    ) + 0 ,

        ISFILTERED ( StartDatesDim ) = FALSE() && ISFILTERED ( Enddatesdim ) =TRUE () ,

            CALCULATE (

                    COUNT ('Newest agreement by employee'[Workagreement.id] )  ,

                    StartDatesDim[Start date] <= _firstday ,

                   -- Enddatesdim[End date] >= SLenddate || Enddatesdim[End date] = BLANK() ,

                    _modefilter

                    ) + 0,

        ISFILTERED ( StartDatesDim ) = TRUE() && ISFILTERED ( Enddatesdim ) =FALSE() ,

            CALCULATE (

                    COUNT ('Newest agreement by employee'[Workagreement.id] )  ,

                    StartDatesDim[Start date] <= SLstartdate ,

                    Enddatesdim[End date] >= _lastday || Enddatesdim[End date] = BLANK() ,

                    _modefilter

                    ) + 0,

        ISFILTERED ( StartDatesDim ) = FALSE() && ISFILTERED ( Enddatesdim ) =FALSE() ,

            CALCULATE (

                    COUNT ('Newest agreement by employee'[Workagreement.id] )  ,

                    StartDatesDim[Start date] <= DATE ( YEAR ( TODAY()) , 1, 1 ) ,

                    Enddatesdim[End date] >= _lastday || Enddatesdim[End date] = BLANK() ,

                    _modefilter

                    ) + 0

)

RETURN

Result

[henkka]

here is the newest agreement by employee table if you wish to try this.

table https://docs.google.com/spreadsheets/d/1_jJoFQdCYnZILWdGZWrSq8sk5vgSxyafQ-tPMk5cVnA/edit?usp=sharing

[MAwwad]

One issue I see is that in the second case of the SWITCH statement, where the StartDatesDim filter is not applied but the Enddatesdim filter is applied, you are not using the SLenddate variable to filter the Enddatesdim[End date] column. Instead, you are using the fixed date _lastday, which is the end of the previous month. This means that the measure will always return the same result in this case, regardless of the value selected in the Enddatesdim filter.

To fix this, you can update the second case of the SWITCH statement to use the SLenddate variable to filter the Enddatesdim[End date] column, like this:

SWITCH ( TRUE() , ISFILTERED ( StartDatesDim) = TRUE() && ISFILTERED(Enddatesdim) = TRUE() , CALCULATE ( COUNT ('Newest agreement by employee'[Workagreement.id] ) , StartDatesDim[Start date] <= SLstartdate , Enddatesdim[End date] >= SLenddate || Enddatesdim[End date] = BLANK() , _modefilter ) + 0 , ISFILTERED ( StartDatesDim ) = FALSE() && ISFILTERED ( Enddatesdim ) =TRUE () , CALCULATE ( COUNT ('Newest agreement by employee'[Workagreement.id] ) , StartDatesDim[Start date] <= _firstday , Enddatesdim[End date] >= SLenddate || Enddatesdim[End date] = BLANK() , _modefilter ) + 0, ISFILTERED ( StartDatesDim ) = TRUE() && ISFILTERED ( Enddatesdim ) =FALSE() , CALCULATE ( COUNT ('Newest agreement by employee'[Workagreement.id] ) , StartDatesDim[Start date] <= SLstartdate , Enddatesdim[End date] >= _lastday || Enddatesdim[End date] = BLANK() , _modefilter ) + 0, ISFILTERED ( StartDatesDim ) = FALSE() && ISFILTERED ( Enddatesdim ) =FALSE() , CALCULATE ( COUNT ('Newest agreement by employee'[Workagreement.id] ) , StartDatesDim[Start date] <= DATE ( YEAR ( TODAY()) , 1, 1 ) , Enddatesdim[End date] >= _lastday || Enddatesdim[End date] = BLANK() , _modefilter ) + 0 )

[henkka]

Hi, and thanks for your reply. The behaviour is exactly the same if I try to copy and paste your suggestion for the switch statement.

I think this issue is something how those filters are updated in the calculation because based on my debugging it seems that DAX is using the correct switch step

[henkka · Accepted solution]

Hi light bulb just switched on. My issue is not in this DAX but from where I'm trying to get the answer from. I have aggregated table "newest agreement by employee" which is an aggregated table and only includes the valid agreements today. But if there has been previous agreements that has been expired those are in the workagreements table which hold all the agreements made to the company.

So this issue is not at all about the DAX but the desing of approach at the firs place. Thank you @MAwwad for your effort to try and help with the issue and sorry to give you an impossible puzzle to solve 🙂