topic Re: referencing columns from calculated table and calling a measure in DAX Commands and Tips

referencing columns from calculated table and calling a measure

scabral — Fri, 02 Oct 2020 17:20:27 GMT

Hi,

i created a calculated table called Table1 using DAX with 3 columns (Claim Id, LocationKey, and Calendar Date). Now what i want to do is use that calculated table as a context when calling a measure called GrossReserveAmt and return all 3 columns from the calculated table and the GrossReserveAmt calculated for each row from the table. I tried to use summarize, but i can't get to teh columns of the table and it just gives me the total amount of the GrossReserveAmt measure.

thanks

Scott

Re: referencing columns from calculated table and calling a measure

lbendlin — Sat, 03 Oct 2020 00:51:12 GMT

measures return a scalar value - always. You can use table variables during the computation of the measure, but the end result has to be a scalar.

Rethink your process. Use CONCATENATEX() if you must.

Re: referencing columns from calculated table and calling a measure

scabral — Sat, 03 Oct 2020 01:10:18 GMT

Ok, so if i create a calculated table using the same logic and then create a calculated column to call the meausure for each row. RIght now it's returning the total value for each row. How do i change the context when calling the measure so that it uses the row context (claim id, location id, and date) when calculating the measure?

Re: referencing columns from calculated table and calling a measure

stevedep — Sat, 03 Oct 2020 12:23:34 GMT

scabral , what is the requirement behind the requirement? Perhaps the approach should be different.

Re: referencing columns from calculated table and calling a measure

scabral — Sat, 03 Oct 2020 14:16:20 GMT

Hi Stevedep,

i tried a couple of different ways to accomplish this, but basically i created a measure that accepts a date (AsOfDt) and the measure gets the latest version of a reserve for each claim/damage/location where the reserve date is <= the AsOfDate. So let's call that measure GrossClaimReserve. Here is the actual code for the measure:

VAR AsOfDtId =
    MAX ( 'As Of Date'[Dim Date Identifier] )
VAR ReserveMaxFilter =
    CALCULATETABLE (
        ADDCOLUMNS (
            SUMMARIZE (
                'Claim Reserve Values',
                'Claim Reserve Values'[Claim ID],
                'Claim Reserve Values'[Damage ID],
                'Claim Reserve Values'[Location ID]
            ),
            "SeqNum", CALCULATE ( MAX ( 'Claim Reserve Values'[Reserve Sequence Num] ) )
        ),
        KEEPFILTERS ( 'Claim Reserve'[Reserve Status ID] = 2 ),
        'Claim Reserve Values'[Reserve Date ID] <= AsOfDtId
    )
RETURN
    CALCULATE (
        SUM ( 'Claim Reserve Values'[100 % Gross Amount USD] ),
        TREATAS (
            ReserveMaxFilter,
            'Claim Reserve Values'[Claim ID],
            'Claim Reserve Values'[Damage ID],
            'Claim Reserve Values'[Location ID],
            'Claim Reserve Values'[Reserve Sequence Num]
        ),
        'Claim Reserve'[Reserve Group ID]
            IN { 1000007, 1000008 }
    )

this measure works and i can call this measure using the following code and it gives me the latest reserve for each claim/damage/location for the min AsOfDt in context:

VAR MinAsOfDate = MIN('As Of Date'[Dim Date Identifier])

VAR ValueMin = CALCULATE([Gross Claims Reserve USD], 'As Of Date'[Dim Date Identifier]=MinAsOfDate)

Return

ValueMin

Now for another purpose i need to create a separate calculated table with claim id, location id and date as the subset. I then need to be able to use that subset of data and calculate the measure above for each row (basically create another column in the calculated table that has the GrossReserveAmt for that row). So i created the calculated table as follows:

evaluate
    VAR reserveValueDates =
    SUMMARIZE (
        FILTER (
            'Claim Reserve Values',
            RELATED ( 'Claim Reserve'[Reserve Group ID] ) IN { 1000007, 1000008 }
                && RELATED ( 'Claim Reserve'[Reserve Status ID] ) = 2
                && NOT ( ISBLANK ( RELATED ( 'Claim Adjustment File'[Time To Settle Date] ) ) )
        ),
        'Claim Reserve Values'[Claim ID],
        'Claim Reserve Values'[LocationKey],
        'Claim Reserve Date'[Dim Date Identifier]
    )
VAR reserveValueDatesToAsOfLineage =
    TREATAS (
        reserveValueDates,
        'Claim Reserve Values'[Claim ID],
        'Claim Reserve Values'[LocationKey],
        'As Of Date'[Dim Date Identifier]
    )

i end up with the last VAR of reserveValueDatesToAsOfLineage that give me Claim Id, Location Id, and As Of Date and now i want to use that VAR table as a context to call the GrossClaimsReserve Measure and give me another column with the results of that meausre for each row in the reserveValueDatesToAsOfLineage Table.

Thanks

Scott

Re: referencing columns from calculated table and calling a measure

stevedep — Sat, 03 Oct 2020 14:23:30 GMT

Whats is the business requirement?

Re: referencing columns from calculated table and calling a measure

scabral — Sat, 03 Oct 2020 14:33:30 GMT

HI Stevedep,

The requiremnt is to figure out what the total reserve amount is for each claim at a specified point in time (the AsOfDt). Then compare that reserve amount to the actual payment amount of the claim (which i have not included yet) to see what the percentage is between both. the first step is to get the latest reserve amount for each claim based on the asofdt so that is what i am trying to figure out here. Once i get that, i can bring in the payment amount and do the division to get the percentage.

Re: referencing columns from calculated table and calling a measure

scabral — Sat, 03 Oct 2020 14:40:04 GMT

i also tried to just use AddColumns to call the measure for the table liek this:

VAR accuracy =
        ADDCOLUMNS(
            reserveValueDatesToAsOfLineage,
            "ReserveAmt", [NewGrossClaimsReserveUSD]
        )
        return accuracy

but i get a memory error.