Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
20 | |
19 | |
15 | |
15 |
User | Count |
---|---|
47 | |
28 | |
25 | |
17 | |
17 |