cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## [DAX] Create Table with using group sum

Hello
I want to Create DAX Table

Table1

 ModuleID StatusScore A1 0 A1 0 A1 1 A2 0 A2 0 A2 0 A3 1 A3 10 A3 1 A4 0 A4 1 A4 0 A5 0 A5 0 A5 0

TableT2 (Remove)

 ModuleID A1

I want to this table

 ModuleID SUMStatusScore A3 12 A4 1

Create Table=
VAR x0=SUMMARIZE(Table1,Table1[ModuleID],"SUM_",SUM(Table1[StatusScore]))
VAR x1=FILTER(x0,[SUM_]>0)
VAR x2=CALCULATETABLE(x1,FILTER(Table2,Table2[ModuleID]<> ?? )
RETURN

x2

How Can i write the DAX code ?

1 ACCEPTED SOLUTION
Super User

@ChoiJunghoon , Try like

Create Table=
VAR x0=SUMMARIZE(filter(Table1, not(Table1[ModuleID] in values(Table2[ModuleID])) ,Table1[ModuleID],"SUM_",SUM(Table1[StatusScore]))
VAR x1=FILTER(x0,[SUM_]>0)
RETURN
x1

3 REPLIES 3
Super User

New Table =
VAR removed_table =
FILTER ( Table1, NOT ( Table1[ModuleID] IN VALUES ( Table2[ModuleID] ) ) )
VAR remove_zero =
FILTER ( removed_table, CALCULATE ( SUM ( Table1[StatusScore] ) ) <> 0 )
VAR groupby_sum =
GROUPBY (
remove_zero,
Table1[ModuleID],
"@SUMStatusScore", SUMX ( CURRENTGROUP (), Table1[StatusScore] )
)
RETURN
groupby_sum

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Super User

@ChoiJunghoon , Try like

Create Table=
VAR x0=SUMMARIZE(filter(Table1, not(Table1[ModuleID] in values(Table2[ModuleID])) ,Table1[ModuleID],"SUM_",SUM(Table1[StatusScore]))
VAR x1=FILTER(x0,[SUM_]>0)
RETURN
x1

Helper III

Thank you!! Thank you!!