DAX Calculated Measure based on Month

Question

I have a Opening Count Measure (Given Below). The Logic which I have applied is:

If you looking for the Opening Count for Jan-2017 then Merge1[Loggd_dt] should be before 01 Jan 2017 and Merge1[Cmplt_dt] values should be from 01 jan 2017 (The data is from year 2005 upto date and the logic is to be applied to overall data). I am taking this measure in a matrix(along with two other measures). I have also applied some slicer in the same page. My other two measures are changing according to the value selected in Slicer. But the measure given below is not changing according to slicer.

Measure 1:

Opening Count = 
IF (
    HASONEVALUE ( DateTable[StartOfMonthDate] ),
    CALCULATE (
        [Count_rows],
        FILTER (
            ALL ( Merge1 ),
            (
                Merge1[LOGGD_DT]
                    < CALCULATE (
                       VALUES ( DateTable[StartOfMonthDate] ),
                       USERELATIONSHIP ( DateTable[Date], Merge1[LOGGD_DT] )
                )
                && Merge1[CMPLT_DT]
                    >= CALCULATE (
                        VALUES ( DateTable[StartOfMonthDate] ),
                        USERELATIONSHIP ( DateTable[Date], Merge1[CMPLT_DT] )
                        )
            )
        )
    )
)

Measure 2:

Closed Count = 
CALCULATE (
    [Count_rows],
    USERELATIONSHIP ( DateTable[Date], Merge1[CMPLT_DT] ),
    Merge1[STATUS] = "CL"
)

Measure 3:

InProcess Count = 
CALCULATE (
    [Count_rows],
    USERELATIONSHIP ( DateTable[Date], Merge1[LOGGD_DT] )
)

Measure used:

Count_rows = COUNTROWS(Merge1)

Marco Vos Marco Vos · Accepted Answer · 2018-10-09T09:25:03

The problem is probably the ALL Function. I think it overrides the filter in your slicer.
Try ALLEXCEPT ( 'Merge1'[put slicer-column here] ) in stead of ALL ( Merge1 )

DAX Calculated Measure based on Month

1 Answers