How to change a DAX filter based on the Date node (year or month) viewed?

[D_PBI]

Hi,
I have the below measure that counts the number of distinct IDs where the record has a Status of 'Closed', and the record's Last Active Date falls within the calendar month for __dimDate_CaseDate - as it is the Case Date's Year and Month that is configured in the 'Rows' section of the Matrix visual.

This works correctly if the measure is being viewed along the Month node - see below. The value of 1 is expected and this should be present against the month of October. No other months have a record that meets this criteria.

However, when I collapse the node and want to show the same measure against the Financial Year, I would expect a value of 1 to show as we know this belongs to the month of October and there are no other months, in that Financial Year, that have counts. What I see is the value of 5 which is incorrect - see below.

How do I amend the above measure to work whether the Matrix visual is expanded to show months, or collapsed to show only the year level?

[PaulMac]

So is the matrix table acting as you expect now @D_PBI ? Was this just a session session bug?

[D_PBI]

@PaulMac the matrix visual was always working correctly. I thought there was an issue but there wasn't. Maybe it's worth deleting this thread but I'll leave it in case others find the measure useful.

[PaulMac]

Hi
I was unable to recreate your issue exactly but I did run into an issue where by my count of closed ID's was showing the same value for each month but this was fixed as I had yet to set up a realtionship between Date table and Complaints tabel in the Model View. After I did this, the calc that i produced (pretty much a copy of yours) worked fine whether the node was collapsed or expanded.

So maybe check your relationships, if you haven't done so already.

DistinctNumberOfID_Closed = 
 VAR _start = STARTOFMONTH(DimDate[Date])
 VAR _end = ENDOFMONTH(DimDate[Date])
 VAR _result = 
    CALCULATE(
        DISTINCTCOUNT(TblComplaints[Case Reference Number]),
        TblComplaints[CaseStatus] = "Closed",
        AND(
            TblComplaints[Modified Date] >= _start,
            TblComplaints[Modified Date] <= _end
        )
    )
RETURN
    _result + 0

[D_PBI]

@PaulMac thanks for your review. Having taken a step away from this task and coming back to it just now, I see there is not an issue with the Month or Year node calculation. It is working correctly. I guess I wasn't seeing things clearly earlier. Here's hoping it hasn't been a waste of time as this DAX may be useful for others in the future.