Forum Discussion
[_1] syntax
Hello experts, would you explain what the following measure do? Especially syntax "_1" and [_1] .
Measure 3 =
var _cnt = CALCULATE(DISTINCTCOUNT( Sheet1[Course Name]),ALLSELECTED(Sheet1))
return
Sumx(filter(SUMMARIZE(Sheet1,Sheet1[Partner], "_1",DISTINCTCOUNT(Sheet1[Course Name])),[_1]=_cnt),[_1])
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".
2 Replies
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".
The square brackets indicate a column or measure name. In this case [_1] is the name of the column you have created using the SUMMARIZE function: https://docs.microsoft.com/en-us/dax/summarize-function-dax
Measure 3 =
--this line below creates a variable called _cnt that will take the distinct count of course names in the Sheet 1 table, ignoring all 'filters' from the visualization, basically it gives you a grand total count of distinct courses in Sheet 1 based on your slicer selections
var _cnt = CALCULATE(DISTINCTCOUNT( Sheet1[Course Name]),ALLSELECTED(Sheet1))
return
--this line creates a virtual table using the SUMMARIZE function, so it groups the Sheet1 table by Partner (creating 1 row for each distinct partner),
Sumx(filter(SUMMARIZE(Sheet1,Sheet1[Partner]
--then adds a new column to that virtual table, calling that column "_1", and populating that column with a distinct count of course names for each Partner
, "_1",DISTINCTCOUNT(Sheet1[Course Name]))
-- then it filters the new virtual table so that only the Partners who have the same distinct count of courses as the grand total _cnt we calculated in the variable above are left
,[_1]=_cnt)
-- finally it sums all the distinct counts, so basically takes _cnt * number partners who have same total of distinct courses as _cnt
,[_1])Does that help/make sense?
