Forum Discussion
DAX
Hi PASCALESAAR
The issue is not with your DAX logic but with the structure of the table imported into Power BI. The SINISTRES RÈGLES / CLAIMS RULES table is already the result of SQL joins, which leads to duplicated claim rows when there are multiple matches in the joined tables. SQL handles these duplicates during aggregation, but when you import this table into Power BI and apply additional filters like the CODTYPSO filter from the SORT_SINISTRE table Power BI effectively joins the data again. This causes COUNT measures to match SQL results since DISTINCTCOUNT removes duplicates, but SUM and SUMX give incorrect totals because they sum over the duplicated records.
To verify, check if the fact table has multiple rows per claim ID; if it does, the amounts will not match SQL as long as you use a pre-joined table. The best solution is to import the base fact table and the SORT_SINISTRE table separately, letting Power BI handle the join, which ensures accurate results. If you can't change the data model, you can work around this by deduplicating the fact table in your measure, such as by summarizing at the claim ID level and using a single amount per claim, to better match SQL behavior.
If you have any further questions, please let us know. we can assist you further.
Best Regards.
Hi PASCALESAAR
try measure below:
Montant Dossiers en Stock version 1=
VAR startdate = MIN(Dates[Date])
VAR enddate = MAX(Dates[Date])
-- 1. Identify the Claims (Numesini1) that match your criteria in the SORT table
VAR FilteredNumesini =
CALCULATETABLE(
VALUES(SORT_SINISTRE[NUMESINI1]),
SORT_SINISTRE[CODTYPSO] IN {"AN", "TR"}
)
RETURN
CALCULATE(
-- 2. Simply SUM the amount. No need for SUMX/SUMMARIZE here.
SUM('SINISTRES RÈGLES'[MONTREGL]),
-- 3. Apply your date filters
'SINISTRES RÈGLES'[DATEDECL] >= startdate,
'SINISTRES RÈGLES'[DATEDECL] <= enddate,
-- 4. Apply the filter from the SORT table using TREATAS
-- This virtually propagates the filter from SORT_SINISTRE to SINISTRES RÈGLES
TREATAS(
FilteredNumesini,
'SINISTRES RÈGLES'[NUMESINI1]
)
)If this doesn't work, then provide sample pbix with data.
Please give kudos or mark it as solution once confirmed.
Thanks and Regards,
Praful
Bonjour j'ai essayé ça ne fonctionne toujours pas
Hi PASCALESAAR
Thank you for contacting the Microsoft Fabric community forum.
Use CALCULATE over the fact table and make sure the set of claim IDs from SORT_SINISTRE is filtered by the same date window or apply the date window directly in the CALCULATE. This keeps DAX behavior identical to SQL: apply filters first, then aggregate.Coût Sinistres En Stock = VAR startdate = MIN(Dates[Date]) VAR enddate = MAX(Dates[Date]) ---Build the set of claim IDs that are "in stock" AND are in the date window VAR FilteredNumesini = CALCULATETABLE( VALUES(SORT_SINISTRE[NUMESINI1]), SORT_SINISTRE[CODTYPSO] IN { "AN", "TR" }, -- Important: apply the same date filter to the SORT table if SORT has a date. -- If the date is in SORT_SINISTRE use that column; if not, remove these two lines. SORT_SINISTRE[DATEDECL] >= startdate, SORT_SINISTRE[DATEDECL] <= enddate ) RETURN CALCULATE( SUM('SINISTRES RÈGLES'[MONTREGL]), -- apply the virtual filter from SORT_SINISTRE into SINISTRES RÈGLES TREATAS(FilteredNumesini, 'SINISTRES RÈGLES'[NUMESINI1]), -- Also ensure payments are in the same date window if DATEDECL lives in payments table 'SINISTRES RÈGLES'[DATEDECL] >= startdate, 'SINISTRES RÈGLES'[DATEDECL] <= enddate )
If a relationship exists and filter direction flows from SORT_SINISTRE -> SINISTRES RÈGLES, you can simplify:
Coût Sinistres En Stock (simple) = CALCULATE( SUM('SINISTRES RÈGLES'[MONTREGL]), FILTER( SORT_SINISTRE, SORT_SINISTRE[CODTYPSO] IN { "AN", "TR" } -- + date filter on SORT_SINISTRE if needed ) )
Use this only if you know the relationship is active and direction propagates from SORT to payments.
I hope this information is helpful. If you have any further questions, please let us know. we can assist you further.
Regards,
Microsoft Fabric Community Support Team.
Hi PASCALESAAR
I wanted to check if you’ve had a chance to review the information provided. If you have any further questions, please let us know. Has your issue been resolved? If not, please share more details so we can assist you further.
Thank You.