Referencing a Field Parameters in AllExcept

[teja503]

Hi Folks,

I need your help in referencing a column selected in Filed parameter dynamically in Allexcept to form a grouping along with other static columns, all from a single Table.

I have three slicers "L1", "L2", "L3" all of them have same 10 columns (three field parameters) those three levels are used in a matrix visual

user can select combination of slicer options to view data at different levels in matrix.

The problem here is I need to develop a measure which uses allExcept to create a grouping and and then pick only the min of column "ID" and sum the values.

In All except I need to use couple of static columns and all the three columns selected across those 3 level slicers. I Hva ebrain stromed a lot  but not able to figure out how to get those values dynamically and use inside allexcept.

I tried the option of using selected value but there might be 100 combination of selections from the 3 slicers.

Please elt me know if some more details are required

[teja503]

@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

[OwenAuger]

Ah I see 🙂

If there are calculated columns used in the level slicers, then you are forced to unpivot with a calculated table rather than Power Query.

It's not impossible, but a bit tedious to write the code (you could generate it somehow), and the specific columns required would need to be hard-coded.

You can limit the Attributes to just the 16 columns though.

Here's an example using the Customer table from Contoso Light on dax.do.

https://dax.do/pYc6P1cRhPMjFc/

I can't see any other way around doing something like this, as DAX has no way to construct a dynamic column reference 😞

[OwenAuger]

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