Forum Discussion

koorosh's avatar
koorosh
Post Partisan
5 years ago
Solved

[_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)) retu...
  • nandukrishnavs's avatar
    5 years ago

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