Tag Archives: dax query

PowerBI: Change calculation on child value of Pivot

Measure = 
VAR IsLowestLevel =
    HASONEVALUE(Table[RowColumn1]) &&
    HASONEVALUE(Table[RowColumn2]) &&
    HASONEVALUE(Table[RowColumn3]) &&
    HASONEVALUE(Table[RowColumn4]) &&
    HASONEVALUE(Table[RowColumn5])

VAR Is2ndToTheLowestLevel =
    HASONEVALUE(Table[RowColumn1]) &&
    HASONEVALUE(Table[RowColumn2]) &&
    HASONEVALUE(Table[RowColumn3]) &&
    HASONEVALUE(Table[RowColumn4]) 



RETURN
IFERROR(IF (
    IsLowestLevel,
    [BaseColumnToCompute1]/
    SUMX(
        FILTER(
            ALL(Table),
            Table[GlobalFilter1] = SELECTEDVALUE(Table[GlobalFilter1]) &&            
            Table[RowColumn1] = SELECTEDVALUE(Table[RowColumn1]) &&
            Table[RowColumn2] = SELECTEDVALUE(Table[RowColumn2]) &&
            Table[RowColumn3] = SELECTEDVALUE(Table[RowColumn3]) &&
            Table[RowColumn3] = SELECTEDVALUE(Table[RowColumn4])
        ),
        [BaseColumnToCompute1]+500 /*Sample custom compute*/
    ),
    IF (
        Is2ndToTheLowestLevel,
        [BaseColumnToCompute1]/
        (SUMX(
            FILTER(
                ALL(Table),
                Table[GlobalFilter1] = SELECTEDVALUE(Table[GlobalFilter1]) &&            
                Table[RowColumn1] = SELECTEDVALUE(Table[RowColumn1]) &&
                Table[RowColumn2] = SELECTEDVALUE(Table[RowColumn2]) &&
                Table[RowColumn3] = SELECTEDVALUE(Table[RowColumn3])
            ),
            [BaseColumnToCompute1]+1000 /*Sample custom compute*/
        )),
        [DefaultBaseColumnToCompute1]
    )
),0)