How to create the new columns in the variable table....

[ChoiJunghoon]

Hello.

I have a issue again.

Table1 

ModuleID	| StatusScore	| Sequence	| Loction
A1	0	1	A
A1	0	2	B
A1	1	3	C
A2	0	5	D
A2	0	3	E
A2	0	4	F
A3	1	1	A
A3	10	2	B
A3	1	3	C
A4	0	4	D
A4	20	5	E
A4	0	6	F
A5	0	7	A
A5	0	8	B
A5	0	9	C

Table2 (remove)

ModuleID

A1

................................................................................................................

I would like to create the table by using DAX as like below it 

ModuleID	| SUMStatusScore	| LastSequence	| Location
A3	12	5	D
A4	20	6	F

Create Table=
VAR x0=SUMMARIZE(filter(Table1, not(Table1[ModuleID] in values(Table2[ModuleID])) ,Table1[ModuleID],"SUM_",SUM(Table1[StatusScore]),"LastSequence",MAX(Table1[Sequence])

VAR x1=ADDCOLUMNS(x0, "Location", LOOKUPVALUE(Table1[Loction],Table1[ModuleID], ?????)
RETURN
x1

how can i write the DAX ?

[Samarth_18]

Hi @ChoiJunghoon ,

You can create a custom column in table with below :-

filter =
VAR result =
    CALCULATE (
        FIRSTNONBLANK ( Table1[ModuleID], 1 ),
        FILTER (
            ALL ( 'Table2(remove)' ),
            'Table1'[ModuleID] = 'Table2(remove)'[ModuleID]
        )
    )
RETURN
    IF ( result <> BLANK (), "Remove", "Add" )

2. Add all required column and add new filter column as filter :-

Thanks,

Samarth

[ChoiJunghoon]

Thank you for your reply. 
But I must create the table by using DAX.

[Samarth_18]

Okay, then you can create a table with below dax:-

VAR x0 =
SUMMARIZE (
    FILTER (
        Table1,
        NOT ( Table1[ModuleID] IN VALUES ( 'Table2(remove)'[ModuleID] ) )
    ),
    Table1[ModuleID],
    "SUM_", SUM ( Table1[StatusScore] ),
    "LastSequence", MAX ( Table1[Sequence] ),
    "location", MAX ( Table1[Location] )
)

Output:-

Thanks,

Samarth

[ChoiJunghoon]

Thank you for your reply. but. 
Location column can not be applied with the "MAX" function. 

Becuase it's not sequencial. 

A2  5 D 

Could you please check out that. 

[Samarth_18 · Accepted solution]

Okay. Can you try below code now:-

VAR x0 = 
SUMMARIZE (
    FILTER (
        Table1,
        NOT ( Table1[ModuleID] IN VALUES ( 'Table2(remove)'[ModuleID] ) )
    ),
    Table1[ModuleID],
    "SUM_", SUM ( Table1[StatusScore] ),
    "LastSequence", MAX ( Table1[Sequence] ),
    "location",CALCULATE(max(Table1[Location]),Table1[Sequence] = MAX ( Table1[Sequence] ))
)

Output:-

Thanks,

Samarth

[ChoiJunghoon]

Thank you Thank you!!