Forum Discussion
[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 ?
Please help me!!
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
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
x1Thank you!! Thank you!!
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
