Forum Discussion

htbull_'s avatar
htbull_
Frequent Visitor
1 year ago
Solved

Trouble aggregating Measure results in a Table

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?

 

 

General Comment
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    1 year ago

    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
    )
    )


4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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
    )
    )


  • v-veshwara-msft's avatar
    v-veshwara-msft
    Icon for Community Support rankCommunity Support
    1 year ago

    Hi htbull_ ,
    Thanks for posting in Microsoft Fabric Community.

    Just checking in to see if you query is resolved.

    The approach suggested by Anonymous  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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    _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_ 


  • htbull_'s avatar
    htbull_
    Frequent Visitor
    1 year ago

    Hey Anonymous this worked as intended. Thanks!