Using ALL and ALLEXCEPT

DAX DAX Calculations
1

So for my formula to determine the locations with the [Lowest Net Revenue] it is as follows:

Lowest Net Revenue = 
    VAR
        RankingDimension = VALUES( 'Location Code'[Revenue Location])
    VAR
        RankingSelect = [Lowest Ranking Select]
    RETURN
        CALCULATE( [Net Revenue],
            FILTER(RankingDimension,
                RANKX(ALL('Location Code'[Revenue Location]), [Net Revenue], , ASC) <= RankingSelect))

But what I notice is there are locations that I want to exclude because they are simply too new and would naturally in their first year have a negative net revenue but they would only be distinguishable by their name. At best, I could use the Location type column as a filter and do an ‘ALLEXCEPT’. But if I go from doing ‘ALL’ to ‘ALLEXCEPT’ how would that look in terms of the formula or does it even make sense to do it that way. I know I could always right click on the visual and exclude those locations that way but I want to try and keep everything rooted in formula logic.

2

What it sounds like you should do it this case is use a calculated column inside you Location Code table.

You would implement the logic you describe in a specific column and this would break out the location you want it or out of your evaluation.

Then instead of the ALL( Location Code…)

You would use

FILTER( ALL( Location Code[Revenue Location] ), {Calculated Column Logic = TRUE } )…something like this.

What the filter is doing is refining the table to just the location you want to evaluate over.

Give this a try and let me know.

1 Like
3

This makes sense. I have a bit of work to do to the Locations Table. I need to rebuild it from the ground up to include some additional variables that will be critical to filter down to the correct locations based on what I am doing. Example, bringing in the column that tells me if a location is a Civilian Site or a Military Site, and whether the location is an active location versus an inactive location, etc.