topic How to create the new columns in the variable table.... in DAX Commands and Tips

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

ChoiJunghoon — Thu, 09 Dec 2021 06:40:24 GMT

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

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

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 ?

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

Samarth_18 — Thu, 09 Dec 2021 07:01:01 GMT

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

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

ChoiJunghoon — Thu, 09 Dec 2021 07:43:42 GMT

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

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

Samarth_18 — Thu, 09 Dec 2021 08:14:08 GMT

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

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

ChoiJunghoon — Thu, 09 Dec 2021 09:09:17 GMT

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.

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

Samarth_18 — Thu, 09 Dec 2021 09:48:37 GMT

Okay. Can you try below code now:-

Output:-

Thanks,

Samarth

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

ChoiJunghoon — Thu, 09 Dec 2021 12:01:57 GMT

Thank you Thank you!!