Referencing a Field Parameters in AllExcept

Hi teja503 

If I've understood you correctly, you want to write an expression equivalent to:

CALCULATE (
    <Expression>,
    ALLEXCEPT (
        YourTable,
        YourTable[Fixed Column 1],
        YourTable[Fixed Column 2],
        <Field parameter L1 Column>,
        <Field parameter L2 Column>,
        <Field parameter L3 Column>
    )
)

Limitations of field parameters

Unfortunately it's not possible to create a dynamic column reference based on field parameter selections within a DAX expression.

Power BI (as a client tool) changes references within the visual's DAX query based on field parameter selections, but within a measure expression you cannot turn those selections into dynamic column references.

More generally, I believe it is impossible to write a DAX expression where the lineage of the column references is not determined in advance.

Alternative method

However, there is a way to produce this behaviour using field parameters combined with an unpivoted version of the table containing the field parameter columns.

Here's a small example with three options on each field parameter (PBIX attached):

1. Assume we have a Product table:

2. Next, create field parameters L1, L2 & L3, each containing Brand, Colour and Size.

Here's L1 for example:

3. Now add a table 'Product Unpivot', produced by unpivoting all columns except Product ID.

Here's an extract:

4. Create a 1:many bidirectional relationship betwen 'Product'[Product ID] and 'Product Unpivot'[Product ID].

5. Create a base measure

Product Count = 
COUNTROWS ( 'Product' )

6. Assume we now want to create a measure with this pseudocode:

Product Count ALLEXCEPT Source, L1, L2 =
CALCULATE (
    [Product Count],
    ALLEXCEPT (
        YourTable,
        YourTable[Source],
        <Field parameter L1 Column>,
        <Field parameter L2 Column>
    )
)

We can write it as follows:

Product Count ALLEXCEPT Source, L1, L2 = 
VAR L1_Selection =
    SELECTCOLUMNS ( L1, L1[L1] )
VAR L2_Selection =
    SELECTCOLUMNS ( L2, L2[L2] )
VAR AttributeValue =
    SUMMARIZE (
        'Product Unpivot',
        'Product Unpivot'[Attribute],
        'Product Unpivot'[Value]
    )    
VAR L1_ProductID =
    CALCULATETABLE (
        VALUES ( Product[Product ID] ),
        AttributeValue,
        TREATAS (L1_Selection, 'Product Unpivot'[Attribute] ),
        REMOVEFILTERS ( 'Product' )
    )

VAR L2_ProductID =
    CALCULATETABLE (
        VALUES ( Product[Product ID] ),
        AttributeValue,
        TREATAS ( L2_Selection, 'Product Unpivot'[Attribute] ),
        REMOVEFILTERS ( 'Product' )
    )
VAR Result =
    CALCULATE (
        [Product Count],
        
        -- Below rows are equivalent to
        --   ALLEXCEPT ( 'Product', 'Product'[Source], <L1 column>, <L2 column> )
        ALLEXCEPT ( 'Product', 'Product'[Source] ), -- always include Product[Source]
        L1_ProductID,
        L2_ProductID
    )
RETURN
    Result

• This measure takes the attribute-value pairs from the current filter context (AttributeValue).
• These attribute-value pairs are then applied as filters, with the L1 and L2 attribute names applied as filters as well, to produce two sets of Product IDs (L1_ProductID and L2_ProductID).
• Finally we compute [Product Count] with both L1_ProductID and L2_ProductID applied as filters, along with the fixed modifier ALLEXCEPT ( 'Product', 'Product'[Source] ). (Result)

You can verify that with L1 = Brand and L2 = Colour, the above measure returns the same values as this comparison measure:

Product Count ALLEXCEPT Source, Brand, Colour = 
CALCULATE (
    [Product Count],
    ALLEXCEPT ( 'Product', 'Product'[Source], 'Product'[Brand], 'Product'[Colour] )
)

Can something like this be adapted to your model?

Admittedly this is a fair bit of work. Perhaps we will be able to dynamically reference field selections in measures in the future.

Regards

OwenAuger  Thank you very much for your detailed explanation

Problem I face here is that I have a huge table with around 100 columns and 16 of them are used in the level slicers. Those columns are combination of calculated columns and groupings. To unpivot such a large table with DAX seems to be practically impossible.

Am I missing anything here, help is highly appreciated