Alternative to 'all' that shows only 'selected' items

[btreg]

I am using the following measure in my model:

=maxx(all(Russell_Group[Provider short name]),[Student FTE (Total)])-[Student FTE (Total)]

I have the 'Providers' as rows in my pivot table and the purpose is to show the difference in Student FTE between each provider and top-ranked provider.

My problem is that the 'all' function within the 'maxx' calculation is causing all providers to be included in my pivot table, even those that don't have any Student FTEs, which makes it look a mess.

Is there an alternative to 'all' that would only return the providers that already appear on the pivot table based on existing filters (i.e. ~ 'selected' providers).

Hope this makes sense! Many thanks in advance.

 

[Ozeroth]

Hi btreg,

The problem (as you have described) is that MAXX(ALL(...),...) returns a value regardless of filter context.

The simplest method I can think of is to check if the current value of [Student FTE (Total)] is blank:

1. If you are using a version of DAX without variables:

=
IF (
    NOT ( ISBLANK ( [Student FTE (Total)] ) ),
    MAXX ( ALL ( Russell_Group[Provider short name] ), [Student FTE (Total)] )
        - [Student FTE (Total)]
)

2. If your version of DAX includes variables then you can use this version (to avoid repeated calculation):

=
VAR StudentFTECurrentValue = [Student FTE (Total)]
RETURN
    IF (
        NOT ( ISBLANK ( StudentFTECurrentValue ) ),
        MAXX ( ALL ( Russell_Group[Provider short name] ), [Student FTE (Total)] )
            - StudentFTECurrentValue
    )

 

[btreg]

Hi Owen,

I used 'option 1' and it gives me exactly the result I need - thanks a million for your help!

Ben