Trouble aggregating Measure results in a Table

[htbull_]

Hello all, 

I'm having some trouble in getting some measure results to accurately total and display across all of the desired reporting years. 

I have created a measure to count the rows with a table, which is based on a SaveDate that identifies whether or not a claim was open or closed at the time of reporting. 

Because there are multiple instances of a single claim number (for historical comparison purposes), I created a flag in order to filter the claim numbers on their most recent ID. I have provided the flag measure down below:

All claims have been counted as the latest version of their distinct number (using the flag measure above - using in filter pane) should tally up to determine how many claims were Open or Closed on any specific date. 

Is there a way to count the total across the years, filtering out claims accoring to their StatusID's?

[htbull_]

Hey @DataSteward this worked as intended. Thanks!

[v-veshwara-msft]

Hi @htbull_ ,
Thanks for posting in Microsoft Fabric Community.

Just checking in to see if you query is resolved.

The approach suggested by @DataSteward  is correct. By having _ClaimsOpened respect the calendar relationship and flag logic, and _TotalClaimsOpened use ALL to ignore row context, the calculation ensures that the row-level results and the total are both evaluated correctly.

If you still need assistance, please let us know.
Thank you.

[DataSteward]

_ClaimsOpened :=
CALCULATE(
COUNTROWS(ClaimInfoHistory),


FILTER(
ClaimInfoHistory,
[IsLatestClaimFlag] = 1 &&
ClaimInfoHistory[StatusID] = 352 -- Adjust 352 to desired Open/Closed value
),

Ensure calendar relationship is used correctly
USERELATIONSHIP(ClaimInfoHistory[SaveDate], Calendar_History[Date])
)

_TotalClaimsOpened :=
CALCULATE(
COUNTROWS(ClaimInfoHistory),
FILTER(
ALL(ClaimInfoHistory), -- removes filters like year
[IsLatestClaimFlag] = 1 &&
ClaimInfoHistory[StatusID] = 352
)
)


Hi @htbull_ 

[DataSteward · Accepted solution]

Hi @htbull_ 
Please try this
_ClaimsOpened :=
CALCULATE(
COUNTROWS(ClaimInfoHistory),


FILTER(
ClaimInfoHistory,
[IsLatestClaimFlag] = 1 &&
ClaimInfoHistory[StatusID] = 352 
),

Ensure calendar relationship is used correctly
USERELATIONSHIP(ClaimInfoHistory[SaveDate], Calendar_History[Date])
)


_TotalClaimsOpened :=
CALCULATE(
COUNTROWS(ClaimInfoHistory),
FILTER(
ALL(ClaimInfoHistory), 
[IsLatestClaimFlag] = 1 &&
ClaimInfoHistory[StatusID] = 352
)
)