topic Summing a calculated column using Naturalinnerjoin not working in DAX Commands and Tips

Summing a calculated column using Naturalinnerjoin not working

scabral — Fri, 18 Dec 2020 16:58:46 GMT

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 Addcolumns to create a new calculated column that subtracts 2 original columns from the inner join and that works correctly as well.

What I'm having issues with is summing the calculated column from the joined table variable. I would have thought it was easy, but i keep getting some really large number that doesn't make sense.

Here is my query so far (I am filtering the table variables to just 1 claim id for now):

VAR GrossClaimsReserve =
    SELECTCOLUMNS (
        CALCULATETABLE (
            ADDCOLUMNS (
                SUMMARIZE (
                    'Claim Reserve Values',
                    'Claim Reserve Values'[Claim ID],
                    'Claim Reserve Values'[LocationKey],
                    'Claim Reserve'[Reserve Peril ID],
                    'Claim Damage'[Damage Coverage Category Description]
                ),
                "Gross Claims Reserve USD", [Gross Claims Reserve USD]
            ),
            'Claim Adjustment File'[Claim Number] = "469357"
        ),
        "Claim ID", [Claim ID] + 0,
        "LocationKey", [LocationKey] + 0,
        "Peril ID", [Reserve Peril ID] + 0,
        "Damage Coverage Category Descrition", [Damage Coverage Category Description] & "",
        "Gross Claims Reserve", [Gross Claims Reserve]
    )
VAR Reinsurance =
    SELECTCOLUMNS (
        CALCULATETABLE (
            ADDCOLUMNS (
                SUMMARIZE (
                    'Claim Reinsurance Values',
                    'Claim Reinsurance Values'[Claim ID],
                    'Claim Reinsurance Values'[LocationKey],
                    'Claim Reinsurance Values'[Peril ID],
                    'Claim Damage'[Damage Coverage Category Description]
                ),
                "Gross Reinsurance USD", CALCULATE ( SUM ( 'Claim Reinsurance Values'[Reinsurance Amount USD] ) )
            ),
            'Claim Adjustment File'[Claim Number] = "469357"
        ),
        "Claim ID", [Claim ID] + 0,
        "LocationKey", [LocationKey] + 0,
        "Peril ID", [Peril ID] + 0,
        "Damage Coverage Category Descrition", [Damage Coverage Category Description] & "",
        "Gross Reinsurance USD", [Gross Reinsurance USD]
    )
VAR TableJoin =
SUMX(
    ADDCOLUMNS (
        NATURALINNERJOIN ( GrossClaimsReserve, Reinsurance ),
        "Net Net Reserve USD", [Gross Claims Reserve] - [Gross Reinsurance USD]
    ),
    [Net Net Claims Reserve USD])

my issue is that in the VAR TableJoin the SUMX gives me some really large number that i don't understand how it's computing it. Here are the values in the TableJoin Variable before the SUMX ([Net Net Reserve USD] is add using the ADDCOLUMNS):

The SUMX should just be summing the [Net Net Reserve USD] column and i should get -48,627,000. But instead it gives me

82,087,844,992.0295

and i have no idea why?

thanks

Scott

Re: Summing a calculated column using Naturalinnerjoin not working

edhans — Fri, 18 Dec 2020 17:28:52 GMT

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.

Re: Summing a calculated column using Naturalinnerjoin not working

scabral — Fri, 18 Dec 2020 17:43:30 GMT

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

Re: Summing a calculated column using Naturalinnerjoin not working

edhans — Fri, 18 Dec 2020 17:56:58 GMT

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.

Re: Summing a calculated column using Naturalinnerjoin not working

scabral — Fri, 18 Dec 2020 18:15:48 GMT

Yes, that sumx worked when removing ADDCOLUMNS.

Re: Summing a calculated column using Naturalinnerjoin not working

edhans — Fri, 18 Dec 2020 18:50:08 GMT

Excellent scabral - glad my "shot in the dark" worked there. 😁