Forum Discussion

polman4's avatar
polman4
Helper 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)
polman4's avatar
polman4
Helper I
3 years ago

Hello ReneMoawad 

Sorry for the late reply but was OOF.

 

The solution i'm using was originally being created by v-yalanwu-msft. 

Didn't found out how to include attachments so i'm sending a sample file with wetransfer

https://we.tl/t-E63Ys9ICXY

 

So if you click to any checkbox, you can see that the data doesn't really make sense and i'm suspecting the function "ALL" is causing this.

 

Kind regards,

Greg

ReneMoawad's avatar
ReneMoawad
Resolver 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
    Helper 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
      Resolver 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)