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,
La logique pour « en stock » doit être appliquée dans CALCULATE, afin que le filtre soit imposé avant la somme.
(traduction automatique via Internet), sorry ...
Exemple simple :
Coût Sinistres En Stock =
CALCULATE(
SUM(sinistres_regles[Montant]),
sinistres_regles[Statut] = "En Stock"
)
Si la condition est « date de clôture vide » :
Coût Sinistres En Stock =
CALCULATE(
SUM(sinistres_regles[Montant]),
FILTER(
sinistres_regles,
ISBLANK(sinistres_regles[DateCloture])
)
)
Pourquoi ?
En SQL, le filtre est appliqué dans le WHERE avant l’agrégation. En DAX, si vous ne l’appliquez pas correctement, le contexte peut inclure des lignes hors stock.
Utiliser CALCULATE recrée ce comportement.
If this response was helpful in any way, I’d gladly accept a 👍much like the joy of seeing a DAX measure work first time without needing another FILTER.
Please mark it as the correct solution. It helps other community members find their way faster (and saves them from another endless loop 🌀.
en stock ici est une condition qui provient de la table sort_sinistre (SORT_SINISTRE[CODTYPSO] IN {"AN", "TR"} ) qui est liée à la table des sinistres.
la table sinistres_regles ne concerne que les montants réglés, et est elle aussi liée à la table des sinistres
la table sinistres est le pont entre les deux tables.