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

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.

  • scabral's avatar
    scabral
    Helper IV
    5 years ago

    Yes, that sumx worked when removing ADDCOLUMNS.