How to refer to columns values of a table variable

[Razan_Omoush]

Hi All,

I am trying to find the right formula in DAX for this and getting nowhere.
I want to create a measure based on date slicer selection,
the measure should calculate the claim amount for the claims that occurred on a date before the Max selected value of a Date slicer for only the claims in the period after the Max selected value of a Date slicer. In other words, I want my measure to do the following.

OS Amount=
VAR MAX_DATE_SLICER=CALCULATE (
MAX( 'Date'[Date] )
)
Var Ids_After_max_Date=ADDCOLUMNS ( CALCULATETABLE(
SUMMARIZE(CLAIM_TRANSACTION,CLAIM_TRANSACTION[CLAIM_ID]),
FILTER( CLAIM_TRANSACTION,CLAIM_TRANSACTION[TRANS_DATE] >= MAX_DATE_SLICER) ,
FILTER( CLAIM,CLAIM[CLAIM_STATUS]="Treatment Delivered" ) //// This is a related table 
),
"CLM_ID", CLAIM_TRANSACTION[CLAIM_ID]
)

Var Final_Amount= CALCULATE(SUM(CLAIM_TRANSACTION[TRANS_AMT_LC]),(CLAIM_TRANSACTION[TRANS_DATE] <= MAX_DATE_SLICER),CLAIM[CLAIM_ID] in VALUES(Ids_After_max_Date[CLM_ID] ))
return Final_Amount

my challenge is how to refer to the column values of a table variable. 
I tried to create a calculated table and then refer to its column in my measure using  VALUSES('calculated_table'[CLAIM_ID]) but, I ran into another challenge the selected slicer value did not work with the calculated table and the result was blank table.

this is the calculated table measure I created.
calculated_table =
Var MAX_DATE_SLICER=  CALCULATE (
                                    MAX( 'Date'[Date] )
                                               )
Var IDs_After_Max_Date= CALCULATETABLE(
                SUMMARIZE(CLAIM_TRANSACTION,CLAIM_TRANSACTION[CLAIM_ID]),
                FILTER( CLAIM_TRANSACTION,CLAIM_TRANSACTION[TRANS_DATE] >= MAX_DATE_SLICER)
                )
return IDs_After_Max_Date    

[Daniel29195 · Accepted solution]

@Razan_Omoush 

this is a modified version of your code 

OS Amount =
VAR MAX_DATE_SLICER =
    CALCULATE (
        MAX ( 'Date'[Date] )
    )
    
VAR Ids_After_max_Date =
selectcolumns(
    ADDCOLUMNS (
        CALCULATETABLE (
            SUMMARIZE (
                CLAIM_TRANSACTION,
                CLAIM_TRANSACTION[CLAIM_ID]
            ),
            FILTER (
                CLAIM_TRANSACTION,
                CLAIM_TRANSACTION[TRANS_DATE] >= MAX_DATE_SLICER
            ),
            FILTER (
                CLAIM,
                CLAIM[CLAIM_STATUS] = "Treatment Delivered"
            ) //// This is a related table 
        ),
        "CLM_ID", CLAIM_TRANSACTION[CLAIM_ID]
    ),
    "CLM_ID", [CLM_ID]
    )
    
VAR Final_Amount =
    CALCULATE (
        SUM ( CLAIM_TRANSACTION[TRANS_AMT_LC] ),
         CLAIM_TRANSACTION[TRANS_DATE] <= MAX_DATE_SLICER ,
        CLAIM[CLAIM_ID]
            IN  Ids_After_max_Date
    )
    
RETURN
    Final_Amount

let me know if it works for you . 

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅!

It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

[Idrissshatila]

@Daniel29195  check this out.