Hi @bijaymaharjan,
Thank you for reaching out to the Microsoft fabric community forum. Also thanks @MarkLaf, @SamsonTruong, for those insights on this thread. I reproduced the scenario, and it worked on my end. I used my sample data and successfully implemented it.
outcome:
I am also including .pbix file for your better understanding, please have a look into it:
If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.
Thank you for using Microsoft Community Forum.
If you are concerned about subtotals, then you'll want an iterator version:
Incurred_Capped2M =
CALCULATE(
SUMX(
VALUES( 'Table'[Claim_Number] ),
MIN( CALCULATE( SUM( 'Table'[Total_Incurred] ) ), 2000000 )
),
Dates[Date] <= MAX( Dates[Date] )
)
If subtotals don't need to add up correctly (or if you want subtotals to also be capped at 2M):
Incurred_Capped2M_BadSubtotals =
CALCULATE(
MIN( CALCULATE( SUM( 'Table'[Total_Incurred] ) ), 2000000 ),
Dates[Date] <= MAX( Dates[Date] )
)
In action (dates slicer is just showing end of month dates):
Tldr, you need to introduce a separate Dates table (or add Policy Year/Month columns to Claims table to use in matrix) so the slicer selection does not interfere with your matrix rows.
The challenge is that, when you have Dates[Date] filtering Claims[Policy_Eff_Date], the intersection of the filter context from visual and filter context from slicer will give you undesired results.
Something like the below almost gets what you want:
Transaction Grouped By Policy =
VAR _selectDate = CALCULATE( MAX( Dates[Date] ), ALLSELECTED( Dates ) ) RETURN
CALCULATE(
SUMX(
VALUES( Claims[Claim_Number] ),
MIN( 2000000,
CALCULATE(
SUM( Claims[Total_Incurred] ),
REMOVEFILTERS( Dates ),
Claims[Transaction_Date] <= _selectDate
)
)
),
USERELATIONSHIP( Claims[Policy_Eff_Date], Dates[Date] )
)
You get what you want when nothing is selected in slicer:
You can see the problem more practically, though, if you think through the filter context at the highlight when you have '2/28/2025' selected in the slicer.
The filter is the following, on either Claims[Transaction_Date] or Claims[Policy_Eff_Date]:
- YEAR( Dates[Date] ) = 2024
- && MONTH( Dates[Date] ) = January
- && Dates[Date] = 2/28/2025
The problem isn't our internal sum calculate, as we tell it to ignore all the filters on Dates (REMOVEFILTERS) and just use our new Claims[Transaction_Date] <= _selectDate filter.
The issue is that there is no way to say to the iterator (VALUES( Claims[Claim_Number] ) where Dates[Date] filters Claims[Policy_Eff_Date]) that we want to keep filters from the visual but ignore filters from the slicer (something like the inverse of what ALLSELECTED does). So, our slicer selection will result in a blank visual (there are no policy dates on 2/28/2025).
To fix this, we need another Dates table, disconnected from the rest of the model. Calling it Dates_Select.
Dates_Select = Dates
Then, our measure can work if we switch out the filter on the inner sum. And since Dates_Select is disconnected from Claims, making selections won't impact our matrix rows.
Transaction Grouped By Policy_fixed =
VAR _selectDate = MAX( Dates_Select[Date] ) RETURN
CALCULATE(
SUMX(
VALUES( Claims[Claim_Number] ),
MIN( 2000000,
CALCULATE(
SUM( Claims[Total_Incurred] ),
REMOVEFILTERS( Dates ),
Claims[Transaction_Date] <= _selectDate
)
)
),
USERELATIONSHIP( Claims[Policy_Eff_Date], Dates[Date] )
)
Helpful resources
Join our Fabric User Panel
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Power BI Monthly Update - June 2025
Check out the June 2025 Power BI update to learn about new features.
Fabric Community Update - June 2025
Find out what's new and trending in the Fabric community.
