[_1] syntax

[koorosh]

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])

[nandukrishnavs · Accepted solution]

@koorosh 

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".

[AllisonKennedy]

@koorosh 

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?