ALL function seems weird - HELP

[ChandeepChhabra]

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

[OwenAuger · Accepted solution]

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]

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]

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

😊🍻

[Bibiano_Geraldo]

Perfect