Forum Discussion
[_1] syntax
- 5 years ago
To explain this, I have modified the measure
Measure 3 = VAR _cnt = CALCULATE ( DISTINCTCOUNT ( Sheet1[Course Name] ), ALLSELECTED ( Sheet1 ) ) VAR _summarizedTable = SUMMARIZE ( Sheet1, Sheet1[Partner], "_1", DISTINCTCOUNT ( Sheet1[Course Name] ) ) RETURN SUMX ( FILTER ( _summarizedTable, [_1] = _cnt ), [_1] )"_1" is a column name that you have defined in the SUMMARIZE function.
Below is the syntax of SUMMARIZE
SUMMARIZE (<table>, <groupBy_columnName>[, <groupBy_columnName>]…[, <name>, <expression>]…)The first parameter is Table, then group by column, then a new column name. In your case, you have specified "_1". Then expression. The output of the expression will be stored in the newly created column "_1".
Below is the syntax of SUMX.
SUMX(<table>, <expression>)The first parameter is a table. and the second parameter is an expression. (The expression to be evaluated for each row of the table)
In your example, You wanted to pass a filtered table. That's why you have used FILTER()
Below is the syntax of FILTER()
FILTER(<table>,<filter>)The first parameter is a table. In your original measure, you have directly specified the SUMMARIZE() that will return a table.
To explain this I have stored the result into a variable. Then the second parameter is a filter condition. ie [_1] = _cnt
Now coming to the context of SUMX(). SUMX will return the sum of the "_1" column in the temporary table "_summarizedTable".
To explain this, I have modified the measure
Measure 3 =
VAR _cnt =
CALCULATE ( DISTINCTCOUNT ( Sheet1[Course Name] ), ALLSELECTED ( Sheet1 ) )
VAR _summarizedTable =
SUMMARIZE (
Sheet1,
Sheet1[Partner],
"_1", DISTINCTCOUNT ( Sheet1[Course Name] )
)
RETURN
SUMX ( FILTER ( _summarizedTable, [_1] = _cnt ), [_1] )
"_1" is a column name that you have defined in the SUMMARIZE function.
Below is the syntax of SUMMARIZE
SUMMARIZE (<table>, <groupBy_columnName>[, <groupBy_columnName>]…[, <name>, <expression>]…)
The first parameter is Table, then group by column, then a new column name. In your case, you have specified "_1". Then expression. The output of the expression will be stored in the newly created column "_1".
Below is the syntax of SUMX.
SUMX(<table>, <expression>)
The first parameter is a table. and the second parameter is an expression. (The expression to be evaluated for each row of the table)
In your example, You wanted to pass a filtered table. That's why you have used FILTER()
Below is the syntax of FILTER()
FILTER(<table>,<filter>)
The first parameter is a table. In your original measure, you have directly specified the SUMMARIZE() that will return a table.
To explain this I have stored the result into a variable. Then the second parameter is a filter condition. ie [_1] = _cnt
Now coming to the context of SUMX(). SUMX will return the sum of the "_1" column in the temporary table "_summarizedTable".