Ignore filters on all page based on measure and slicer

[rsanyoto]

Hello all,

Currently  my visual doesnt working because of some wrong dax formula which i am currently using now.

Here's a short explaination based on the image below.

1. this visual uses the measure % Sales. Which has some IF(ISFILTERED) function. see my measure below this emage.

2. We need to filter out some values of the field Level 2 name from a slicer 

3. This filter is applied on this page.

Im guessing that the part of  IF(ISFILTERED) is the cause of the error.

Any suggestions ? should i rewrite the measure?  many thanks!

% sales =

VAR afdeling = 0

VAR directie = AVERAGE('Inhuur externen'[% Norm])

VAR kolom =

SWITCH(

    TRUE(),

        VALUES('MasterKeyTable'[Level 2 name]) = "DGV", 0.069,

        VALUES('MasterKeyTable'[Level 2 name]) = "DGCZ", 0.065,

        VALUES('MasterKeyTable'[Level 2 name]) = "DGLZ", 0.065,

        VALUES('MasterKeyTable'[Level 2 name]) = "plv. SG", 0.113,

        VALUES('MasterKeyTable'[Level 2 name]) = "SG", 0.065,

        VALUES('MasterKeyTable'[Level 2 name]) = "Overig kern", 0.061,

        VALUES('MasterKeyTable'[Level 2 name]) = "Agentschap CBG", 0.104,

        VALUES('MasterKeyTable'[Level 2 name]) = "Centrale Comm. Mensgebonden Onderzoek", 0.065,

        VALUES('MasterKeyTable'[Level 2 name]) = "CIBG", 0.28,

        VALUES('MasterKeyTable'[Level 2 name]) = "De Nederlandse Sportraad", 0.06,

        VALUES('MasterKeyTable'[Level 2 name]) = "Dienst Uitv. Subsidies aan Instellingen", 0.33,

        VALUES('MasterKeyTable'[Level 2 name]) = "Gezondheidsraad", 0.06,

        VALUES('MasterKeyTable'[Level 2 name]) = "IGJ", 0.09,

        VALUES('MasterKeyTable'[Level 2 name]) = "Projectdirectie ALT", 0.114,

        VALUES('MasterKeyTable'[Level 2 name]) = "Raad vd Volksgezondheid en Samenleving", 0.06,

        VALUES('MasterKeyTable'[Level 2 name]) = "Rijksinstituut RIVM", 0.10,

        VALUES('MasterKeyTable'[Level 2 name]) = "Sociaal En Cultureel Planbureau", 0.06,

BLANK())

VAR result =

IF(ISFILTERED('MasterKeyTable'[Level 4 name]),afdeling,

IF(ISFILTERED('MasterKeyTable'[Level 3 name]),directie,

IF(ISFILTERED('MasterKeyTable'[Level 2 name]),kolom,

0.1)))

RETURN result

[v-yalanwu-msft · Accepted solution]

Hi , @rsanyoto ;

According to your description, I tested it and find you should change measure as follows:

% sales = 
VAR afdeling = 0
VAR directie =AVERAGE ( 'Inhuur externen'[% Norm] )
var kolom=SWITCH (
    MAX ( 'MasterKeyTable'[Level 2 name] ),
    "DGV", 0.069,
    "DGCZ", 0.065,
    "DGLZ", 0.065,
    "plv. SG", 0.113,
    "SG", 0.065,
    "Overig kern", 0.061,
    "Agentschap CBG", 0.104,
    "Centrale Comm. Mensgebonden Onderzoek", 0.065,
    "CIBG", 0.28,
    "De Nederlandse Sportraad", 0.06,
    "Dienst Uitv. Subsidies aan Instellingen", 0.33,
    "Gezondheidsraad", 0.06,
    "IGJ", 0.09,
    "Projectdirectie ALT", 0.114,
    "Raad vd Volksgezondheid en Samenleving", 0.06,
    "Rijksinstituut RIVM", 0.10,
    "Sociaal En Cultureel Planbureau", 0.06,
    BLANK ())
VAR result =
    IF (
        ISFILTERED ( 'MasterKeyTable'[Level 4 name] ),
        afdeling,
        IF (
            ISFILTERED ( 'MasterKeyTable'[Level 3 name] ),
            directie,
            IF (ISFILTERED(MasterKeyTable[Level 2 name]),kolom, 0.1 )))
RETURN
    result

The final output is shown below:

The last thing you should note is that if [Level 2 Name] is filtered, it is equivalent to [Level 3 Name] and [Level 4 Name] being filtered, because they belong to the same table, so the result may be slightly different from what you want.

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

This is what code optimized for speed should look like:

% sales =
switch( TRUE(),

    ISFILTERED( 'MasterKeyTable'[Level 4 name] ),
        var afdeling = 0
        RETURN
            afdeling,
    
    ISFILTERED( 'MasterKeyTable'[Level 3 name] ), 
        var directie = AVERAGE( 'Inhuur externen'[% Norm] )
        RETURN 
            directie,
    
    // You can't use ISFILTERED here because
    // your old measure was using VALUES later on
    // and this function would return a table
    // with more than 1 value if the column were
    // filtered with more than 1 value. Hence your
    // comparisons in the old measure were not
    // possible with scalars. And that's why
    // you were getting an error which clearly
    // says that "a table of multiple values
    // was supplied where a single value was
    // expected."
    HASONEFILTER( 'MasterKeyTable'[Level 2 name] ), 
        var Level2name = 
            SELECTEDVALUE(
                'MasterKeyTable'[Level 2 name], 
                "Many Values Visible" 
            )
        VAR kolom =
            SWITCH( TRUE(),
                Level2Name in {"dgv"}, 0.069,
                Level2Name in { "DGCZ", "DGLZ", "SG",
                    "Centrale Comm. Mensgebonden Onderzoek"}, 0.065,
                Level2Name in {"plv. SG"}, 0.113,
                Level2Name in {"Overig kern"}, 0.061,
                Level2Name in {"Agentschap CBG"}, 0.104,
                Level2Name in {"CIBG"}, 0.28,
                Level2Name in { "De Nederlandse Sportraad",
                    "Gezondheidsraad",
                    "Raad vd Volksgezondheid en Samenleving",
                    "Sociaal En Cultureel Planbureau"}, 0.06,
                Level2Name in {"Dienst Uitv. Subsidies aan Instellingen"}, 0.33,
                Level2Name in {"IGJ"}, 0.09,
                Level2Name in {"Projectdirectie ALT"}, 0.114,
                Level2Name in {"Rijksinstituut RIVM"}, 0.10,
                
                // Safety net but should never happen due
                // to the guard clause of this branch (HASONEFILTER).
                Level2Name in {"Many Values Visible"}, -100000
            )
        return
            kolom,
    
    // This will be returned if none of the above
    // is true.
    0.1
)

[amitchandak]

@rsanyoto , Try  a measure like

% sales =

VAR afdeling = 0

VAR directie = AVERAGE('Inhuur externen'[% Norm])

VAR kolom =

SWITCH(

    TRUE(),

        MAX('MasterKeyTable'[Level 2 name]) = "DGV", 0.069,

        MAX('MasterKeyTable'[Level 2 name]) = "DGCZ", 0.065,

        MAX('MasterKeyTable'[Level 2 name]) = "DGLZ", 0.065,

        MAX('MasterKeyTable'[Level 2 name]) = "plv. SG", 0.113,

        MAX('MasterKeyTable'[Level 2 name]) = "SG", 0.065,

        MAX('MasterKeyTable'[Level 2 name]) = "Overig kern", 0.061,

        MAX('MasterKeyTable'[Level 2 name]) = "Agentschap CBG", 0.104,

        MAX('MasterKeyTable'[Level 2 name]) = "Centrale Comm. Mensgebonden Onderzoek", 0.065,

        MAX('MasterKeyTable'[Level 2 name]) = "CIBG", 0.28,

        MAX('MasterKeyTable'[Level 2 name]) = "De Nederlandse Sportraad", 0.06,

        MAX('MasterKeyTable'[Level 2 name]) = "Dienst Uitv. Subsidies aan Instellingen", 0.33,

        MAX('MasterKeyTable'[Level 2 name]) = "Gezondheidsraad", 0.06,

        MAX('MasterKeyTable'[Level 2 name]) = "IGJ", 0.09,

        MAX('MasterKeyTable'[Level 2 name]) = "Projectdirectie ALT", 0.114,

        MAX('MasterKeyTable'[Level 2 name]) = "Raad vd Volksgezondheid en Samenleving", 0.06,

        MAX('MasterKeyTable'[Level 2 name]) = "Rijksinstituut RIVM", 0.10,

        MAX('MasterKeyTable'[Level 2 name]) = "Sociaal En Cultureel Planbureau", 0.06,

BLANK())

VAR result =

IF(ISFILTERED('MasterKeyTable'[Level 4 name]),afdeling,

IF(ISFILTERED('MasterKeyTable'[Level 3 name]),directie,

IF(ISFILTERED('MasterKeyTable'[Level 2 name]),kolom,

0.1)))

RETURN result