Forum Discussion

scabral's avatar
scabral
Helper IV
5 years ago
Solved

Summing a calculated column using Naturalinnerjoin not working

Hi,   i created 2 table variables in a DAX measure with the same column names and took care of any data lineage issues.  I can join the 2 tables correctly using NATURALINNERJOIN.  I also used Addco...
  • edhans's avatar
    edhans
    5 years ago

    All the +0 does is take care of blanks. Using COALESCE(expression, 0) does the same. 

    That doesn't fix data lineage. Data Lineage is a pretty deep topic. Good intro to it here. 

    You can try this for your last variable though:

    MeasureName =
SUMX(
    NATURALINNERJOIN(
        GrossClaimsReserve,
        Reinsurance
    ),
    [Gross Claims Reserve] - [Gross Reinsurance USD]
)

    I don't see the need for that last ADDCOLUMNS. I'm not saying my suggestion will fix it, but it won't destroy any data lineage to this point. Without data though, it is really hard to say. I am not so good at DAX as to be able to visualized all of the tables you are creating in memory and how they flow through.

edhans's avatar
edhans
Community Champion
5 years ago

When you say you took care of data lineage, how? ADDCOLUMNS does not preserve data lineage. See ADDCOLUMNS – DAX Guide for more info, in addition to the other articles referenced from that page - specifically using GENERATE and ROW instead of ADDCOLUMNS and SUMMARIZE - Using GENERATE and ROW instead of ADDCOLUMNS in DAX - SQLBI. Depending on how this is working in your visuals, you may be able to use SUMMARIZECOLUMNS(), though that cannot evaluate filter context, but it is a great query function.


scabral's avatar
scabral
Helper IV
5 years ago

Hi edhans,

 

i had to add the + 0 to the end of the SelectColumns for the table variables in order for the NaturalInnerJoin to work correctly.  So the 2 table variables in my code (GrossClaimsReserve and Reinsurance) are joined together correctly and the ADDCOLUMNS in the TableJoin variables adds the new column correctly as well.  What doesn't work is doing a SUMX on the new column, it gives me some really odd number that is way too large