Measure that keeps one filter while removing another filter both on the same table

[Sebastiaan]

Hi

I need some help with a DAX measure that removes one filter while keeping another filter both on the same table.

The datamodel is about 6 companies each having multiple locations in different municipalities. And each location has a particular capacity. The size of an organisation is the sum of the capacity of all its locations.

In my report I have a dynamic ranking, showing the Top 5 or Top 3 companies in size, depending on whether the user has selected a particular municpality or not (Top 5 when no municipalities are selected and the entire country is shown and Top 3 when one or more municpalities are selected).

Therefore the Organisations table is filtered in two ways:

1. Ranking or not within Top 5 or within Top 3
2. The selected municpalities in the slicer

I would like to calculate the marketshare in the selected area (either one or more municipalities or the entire country when no municipalities are selected).

Therefore I need to remove the filter that determines to show or hide a particular organisation in the Top N Bar chart. And at the same time I need to keep the filter resulting from the selected municpalities, in order to divide the capacity of the organisatie in the selected area by the entire capacity in the selected area.

I tried with ALL, REMOVEFILTER and KEEPFILTER, but I cannot figure out how to keep one filter and remove the other at the same time on the same table

Any help is appreciated.

Marketshare.pbix (36.5 KB)

[Melissa]

Hi @Sebastiaan,

Made a slight adjustment to your measure, give this a go:

Market_share v2 = 
VAR Grand_total_capacity_selectedarea = 
    SUMX(
        CALCULATETABLE(
            FILTER( 'Locations', 
                'Locations'[Location_type] = "A"
            ),
            ALL( Companies )
        ),
        'Locations'[Capacity]
    )
VAR Company_capacity = [Sum_of_Capacity]
RETURN DIVIDE (Company_capacity, Grand_total_capacity_selectedarea, "")

I hope this is helpful.

[Sebastiaan]

@Melissa

Market_share v2 = 
VAR Grand_total_capacity_selectedarea = 
    SUMX(
        CALCULATETABLE(
            FILTER( 'Locations', 
                'Locations'[Location_type] = "A"
            ),
            ALL( Companies )
        ),
        'Locations'[Capacity]
    )
VAR Company_capacity = [Sum_of_Capacity]
RETURN DIVIDE (Company_capacity, Grand_total_capacity_selectedarea, "")

Thanks Melissa, it works. Although I don’t understand exactly why it works. A friend suggested to use ALLEXCEPT as a way to eliminate one filter without the other. Is it for a reason that you choose the approach of CALCULATETABLE?

[Melissa]

@Sebastiaan

Although I don’t understand exactly why it works. Is it for a reason that you choose the approach of CALCULATETABLE?

CALCULATE is a context modifier, if you’re more comfortable using that, this measure will get you the same result. Simply put CALCULATETABLE allows you to do the same but it works on tables.

Market_share v3 = 
VAR Grand_total_capacity_selectedarea = 
CALCULATE(    
    SUMX(
        FILTER( 'Locations', 
            'Locations'[Location_type] = "A"
        ),
        'Locations'[Capacity]
    ), ALL( Companies )
)
VAR Company_capacity = [Sum_of_Capacity]
RETURN DIVIDE (Company_capacity, Grand_total_capacity_selectedarea, "")

I hope this is helpful

[Sebastiaan]

Thanks Melissa!!