Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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?
So is the matrix table acting as you expect now @D_PBI ? Was this just a session session bug?
@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.
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
@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.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
112 | |
93 | |
90 | |
35 | |
35 |
User | Count |
---|---|
154 | |
102 | |
82 | |
64 | |
54 |