Dax function ignores slicer

[polman4]

Hello,

I have the following measure that works fine. 

asdasd

% Pageviews = 
VAR _max =
    CALCULATE (
        MAX ( 'nw_content'[Week_Start_Date] ),
        FILTER (
            ALL ( 'nw_content' ), [Author_Clm] = MAX ( [Author_Clm] ) && [Week_Start_Date] < MAX ( [Week_Start_Date] )
        )
    )
VAR _pir =
    CALCULATE (
        SUM ( [Pageviews] ),
        FILTER ( 
            ALL( 'nw_content' ), [Author_Clm] = MAX ( [Author_Clm] ) && [Week_Start_Date] = _max )
        )
VAR _result =
    DIVIDE ( SUM ( [Pageviews] ), _pir ) - 1
RETURN
    IF ( _result = -1, "no data", _result )

My problem is that when i try to use any filter the results are wrong. From what i understand is the function all i use here: 

ALL( 'nw_content' ), [Author_Clm] = MAX ( [Author_Clm] ) && [Week_Start_Date] = _max ) on variable _pir is ignoring the filter.

Any way to replace the funtion "all" without breaking the whole code?

Thank you!

Grigoris

[polman4]

Hello @ReneMoawad 

Thank you for the reply. Didn't really understand what change to do. What part of code to replace?

Thank you

[ReneMoawad]

Instead of using ALL('nw_content') you can use ALL('nw_content'[Author_Clm], 'nw_content'[Week_Start_Date])

% Pageviews =

VAR _max =

    CALCULATE (

        MAX ( 'nw_content'[Week_Start_Date] ),

        FILTER (

            ALL('nw_content'[Author_Clm], 'nw_content'[Week_Start_Date]), [Author_Clm] = MAX ( [Author_Clm] ) && [Week_Start_Date] < MAX ( [Week_Start_Date] )

        )

    )

VAR _pir =

    CALCULATE (

        SUM ( [Pageviews] ),

        FILTER (

            ALL('nw_content'[Author_Clm], 'nw_content'[Week_Start_Date]), [Author_Clm] = MAX ( [Author_Clm] ) && [Week_Start_Date] = _max )

        )

VAR _result =

    DIVIDE ( SUM ( [Pageviews] ), _pir ) - 1

RETURN

    IF ( _result = -1, "no data", _result )

[polman4]

This gives me back no data for the variable _pir 😕

[ReneMoawad]

Can you please share some of sample data from the tables that you have?

[polman4]

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]

Hello @polman4,

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

Thank you,

[polman4]

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 · Accepted solution]

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]

Hi @polman4 ,

Try using ALL('nw_content'[Author_Clm], 'nw_content'[Week_Start_Date]), ...