Forum Discussion

polman4's avatar
polman4
Icon for Helper I rankHelper I
3 years ago
Solved

Dax function ignores slicer

Hello,   I have the following measure that works fine.  asdasd   % Pageviews = VAR _max = CALCULATE ( MAX ( 'nw_content'[Week_Start_Date] ), FILTER ( ALL ( 'nw_...
Need Help
  • ReneMoawad's avatar
    ReneMoawad
    3 years ago
    Hello polman4,
    Try the below Measure:
     
    diff =
    var _max = CALCULATE(
        MAX('Table'[Column])
        , FILTER(
            ALLEXCEPT('Table', 'Table'[Type])
            , [author] = MAX([author])
            && [Column] < MAX([Column])
        )
    )
    var _pir= CALCULATE(
        SUM([value])
        , FILTER(
            ALLEXCEPT('Table', 'Table'[Type])
            , [author] = MAX([author])
            && [Column] = _max
        )
    )
    var _result= DIVIDE(SUM([value]), _pir) - 1
    return IF(_result = -1, BLANK(), _result)
ReneMoawad's avatar
ReneMoawad
Icon for Resolver III rankResolver III
3 years ago

Hello polman4,

 

the columns name is not clear, can you tell me what is the expected result?

 

Thank you,

polman4's avatar
polman4
Icon for Helper I rankHelper I
3 years ago

Hello ReneMoawad 

Without any filter the column "diff" is working as intended.

So from 362 to 316 it's 12.73% difference.

But if i filter the data, for example i select Roi then the diff column isn't working.

186.70 to 194.01 isn't a 46% decrease..

 

Is it more clear now?

Thank you,

Grigoris

 

 

  • ReneMoawad's avatar
    ReneMoawad
    Icon for Resolver III rankResolver III
    3 years ago
    Hello polman4,
    Try the below Measure:
     
    diff =
    var _max = CALCULATE(
        MAX('Table'[Column])
        , FILTER(
            ALLEXCEPT('Table', 'Table'[Type])
            , [author] = MAX([author])
            && [Column] < MAX([Column])
        )
    )
    var _pir= CALCULATE(
        SUM([value])
        , FILTER(
            ALLEXCEPT('Table', 'Table'[Type])
            , [author] = MAX([author])
            && [Column] = _max
        )
    )
    var _result= DIVIDE(SUM([value]), _pir) - 1
    return IF(_result = -1, BLANK(), _result)