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
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.
la requete DAX que vous m'avez fournie me renvoie le même résultat que celle que j'ai ecrite
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.