Forum Discussion

ChandeepChhabra's avatar
ChandeepChhabra
Impactful Individual
1 year ago
Solved

ALL function seems weird - HELP

Hello Ninjas,

 

Consider this data model.

 

with the following data

 

Data 1 Table

 

Dimension 1 Table

 

And the following measures

 

Sales 1 = SUM('Data 1'[Value])

Rank 1 = RANKX ( ALL('Dimension 1'[Group 2]), [Sales 1],, DESC )

 

 

And the visual comes up like this (where Group1 and Group 2 are coming from the Dimension 1 Table)

 

Nothing unusual here - As expected the ALL function overrides the visual filter and takes all unique values from Group 2 and sustains Group 1 Filter therefore the ranking is done within each value of Group 1.

 

Cool. Now comes another similar model.

 

With the following data

 

Dates Data (Group 2 is intentionally converted to date type)

 

Dates Dim (Group 2 is intentionally converted to date type)

 

And the following measures

Sales 3 = SUM('Dates Data'[Value])
Rank 3 = RANKX( ALL('Dates Dim'[Group 2]), [Sales 3],, DESC )

 

and then this visual (where group 1 and group 2 are coming from the dates dim table)

 

Notice the Rank 3 measure calculates overall Rank and not the rank within the Group 1. 

 

Now my question

  1. Why does the ALL function behaves differently when used with Dates.
  2. If I change the data type in the second model from dates to text or numbers the ALL function behaves as expected.
  3. If I write a query using the CALCULATE function the ALL behaves as expected but not when I use it in an iterator function.

    Here are a few queries

Query on the first model:

 

 

 

Query on the seond model:

 

Notice the behaviour of the ALL function is as expected when used with CALCULATE but not in the case of the CONCATENATEX function.

WHY is that?

 

pbix file

Need Help
  • OwenAuger's avatar
    OwenAuger
    1 year ago

    Hi ChandeepChhabra 

    The short answer is that if:

    • A column of type date is the primary key of a relationship ( 'Dates Dim'[Group 2] in your example ); and
    • A filter is applied to that column within CALCULATE

    then the DAX engine treats the table containing the column of type date as though it had been marked as a date table, and automatically removes filters on that table when applying the filter on the specific date column.

     

    See this SQLBI article.

     

    In your example, within the Rank 3 measure

    1. RANKX iterates over the rows of ALL( 'Dates Dim'[Group 2] ).
    2. For each of those rows, it evaluates the measure [Sales 3].
    3. In the course of evaluating [Sales 3], context transition adds the current row's value of 'Dates Dim'[Group 2] as a filter.
    4. Due to the "automatic date table" behaviour described above, the automatic ALL ( 'Dates Dim' ) removes all filters on 'Dates Dim'.

    So you end up with a rank for each value of 'Dates Dim'[Group 2] which is determined ignoring any other filters on 'Dates Dim', namely the filter on 'Dates Dim'[Group 1] due to grouping in the visual.

     

    A possible fix still using RANKX could be:

    Rank 3 =
VAR DatePartition =
    CALCULATETABLE (
        VALUES ( 'Dates Dim'[Group 2] ),
        REMOVEFILTERS ( 'Dates Dim'[Group 2] )
    )
RETURN
    RANKX (
        DatePartition,
        CALCULATE (
            [Sales 3],
            KEEPFILTERS ( DatePartition )
        ),
        ,
        DESC
    )

    or you could write a measure using RANK specifying partitioning explicity.

4 Replies

  • OwenAuger's avatar
    OwenAuger
    Super User
    1 year ago

    Hi ChandeepChhabra 

    The short answer is that if:

    • A column of type date is the primary key of a relationship ( 'Dates Dim'[Group 2] in your example ); and
    • A filter is applied to that column within CALCULATE

    then the DAX engine treats the table containing the column of type date as though it had been marked as a date table, and automatically removes filters on that table when applying the filter on the specific date column.

     

    See this SQLBI article.

     

    In your example, within the Rank 3 measure

    1. RANKX iterates over the rows of ALL( 'Dates Dim'[Group 2] ).
    2. For each of those rows, it evaluates the measure [Sales 3].
    3. In the course of evaluating [Sales 3], context transition adds the current row's value of 'Dates Dim'[Group 2] as a filter.
    4. Due to the "automatic date table" behaviour described above, the automatic ALL ( 'Dates Dim' ) removes all filters on 'Dates Dim'.

    So you end up with a rank for each value of 'Dates Dim'[Group 2] which is determined ignoring any other filters on 'Dates Dim', namely the filter on 'Dates Dim'[Group 1] due to grouping in the visual.

     

    A possible fix still using RANKX could be:

    Rank 3 =
VAR DatePartition =
    CALCULATETABLE (
        VALUES ( 'Dates Dim'[Group 2] ),
        REMOVEFILTERS ( 'Dates Dim'[Group 2] )
    )
RETURN
    RANKX (
        DatePartition,
        CALCULATE (
            [Sales 3],
            KEEPFILTERS ( DatePartition )
        ),
        ,
        DESC
    )

    or you could write a measure using RANK specifying partitioning explicity.

    • ChandeepChhabra's avatar
      ChandeepChhabra
      Impactful Individual
      1 year ago

      Thanks a lot Owen. I'd been intuitvely working with dual behaviour for a long time until I ran some tests.
      I owe you a beer when we meet. Big fan otherwise 🙏🏻 

      • OwenAuger's avatar
        OwenAuger
        Super User
        1 year ago

        You're welcome Chandeep. Sounds good, looking forward to it!

        😊🍻