Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
i have a SCD type 2 (History) table which has claimhistoryid unique key, this key is connected to fact table using 1 to many relationship. fact is also connected with date dimension as shown in the image below. i need to calculate two measures, one to sum the base transaction amount, which works fine. I need to calculate cumulative total value based on historical records. Also performance is critical because fact and history table are large tables and we use azure as tabular models. see example below, can you help?
Hi @Anonymous
you may try
Cumulative Total =
VAR CurrentID =
MAX ( claimHistory[Claim History ID] )
RETURN
CALCULATE (
[Sum base transaction Amount],
ALLEXCEPT ( claimHistory, claimHistory[ClaimNbr] ),
claimHistory[Claim History ID] <= CurrentID
)
this doesn't work either, this also shows the same result as sum of base transaction amt, it doesn't total at the claim level, just provide total at history level, also poor with performance.
@Anonymous
I guess you are right. It won't work all at one step. Please try
Cumulative Total =
VAR CurrentID =
MAX ( claimHistory[Claim History ID] )
VAR CurrentClaimTable =
CALCULATETABLE (
claimHistory,
ALLEXCEPT ( claimHistory, claimHistory[ClaimNbr] )
)
VAR PreviousIDsTable =
FILTER ( CurrentClaimTable, claimHistory[Claim History ID] <= CurrentID )
RETURN
CALCULATE ( [Sum base transaction Amount], PreviousIDsTable )
@Anonymous , what is cumulative formula
calculate([Sum base transaction Amount]), Filter(allselected('Date') , 'Date'[Date] <= max('Date'[Date])) , filter(allselected('claimHistory'), 'claimHistory'[claimHistoryID] =max('claimHistory'[claimHistoryID]) ) )
this will not work, this gives me the same thing (in my first view) what i do not want. basically i want cumulative total on claim level not on it's history level. we have claimkey in the fact table also but it is not being used to make a relationship, relationship is based on history key which has all current and historical records of the claim.