Forum Discussion
Helper IIIHow to create the new columns in the variable table....
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 ?
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
6 Replies
Samarth_18Community Champion
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
ChoiJunghoonThank 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