topic Re: [DAX] Create Table with using group sum in DAX Commands and Tips

[DAX] Create Table with using group sum

ChoiJunghoon — Thu, 09 Dec 2021 02:02:24 GMT

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

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 ?
Please help me!!

Re: [DAX] Create Table with using group sum

amitchandak — Thu, 09 Dec 2021 03:37:10 GMT

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

Re: [DAX] Create Table with using group sum

Jihwan_Kim — Thu, 09 Dec 2021 04:11:54 GMT

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

Re: [DAX] Create Table with using group sum

ChoiJunghoon — Thu, 09 Dec 2021 04:36:33 GMT

Thank you!! Thank you!!