Dynamic relationships based on the selection

Got an interesting question recently where someone wanted to be able to switch the relationships automatically based on which slicer was selected.

Take the following example:

Here we have the same customer slicer shown twice, in this case we want to slice by customer but if the user uses the ‘baseline customer’ slicer the data should only filter on the ‘Baseline customer’ table and ignore the ‘Real customer’ slicer regardless if it is set.

First thing I do is copy the Customer table to be able to add the same value twice in a slicer using a calculated table:

Baselineregion = DimSalesTerritory

Next I add relationships for both. This is optional for baselineregion, it depends on what else you want to do with it. This solution will ignore it anyway but other measures will be affected.

Now to make things a bit more complicated we add another baseline dimension for regions using the same calc table technique:

The model now looks like this:

Now for the final step some real DAX magic using my new favorite function TREATAS, inspired by Srini one of main DAX developers on Power BI and major DAX overlord. Ready?

This measure will detect which relationship is used and set the according filter.

SalesAmount 2 =
VAR CustomerCodes =
    TREATAS (
        UNION (
            FILTER (
                KEEPFILTERS ( VALUES ( ‘Baseline customer'[CustomerKey] ) ),
                ISCROSSFILTERED ( ‘Baseline Customer’ )
            ),
            FILTER (
                KEEPFILTERS ( VALUES ( ‘DimCustomer'[CustomerKey] ) ),
                NOT ISCROSSFILTERED ( ‘Baseline customer’ )
            )
        ),
FactInternetSales[CustomerKey]
    )
VAR TerritoryCodes =
    TREATAS (
        UNION (
            FILTER (
                KEEPFILTERS ( VALUES ( Baselineregion[SalesTerritoryKey] ) ),
                ISCROSSFILTERED ( ‘Baselineregion’ )
            ),
            FILTER (
                KEEPFILTERS ( VALUES ( ‘DimSalesTerritory'[SalesTerritoryKey] ) ),
                NOT ISCROSSFILTERED ( ‘Baselineregion’ )
            )
        ),
FactInternetSales[SalesTerritoryKey]
    )
RETURN
    CALCULATE (
        SUM ( FactInternetSales[SalesAmount] ),
CustomerCodes,
        ALL ( DimCustomer ),
TerritoryCodes,
        ALL ( DimSalesTerritory )
    )

The trick here is 3 things:

1. We are using TREATAS to join the fact table with the correct dimension table, the results of this join are stored in a variable as a table.
2. We use UNION and ISCROSFILTERED to determine which table to use in the join. You can think of this as an workaround for table expressions not supporting IF. When the ‘baseline customer’ table is filtered it returns a table of selected CustomerKeys from the ‘baseline customer’  table, each row is are FILTERed by “TRUE” (being the result of ISCROSSFILTERED). All rows of regular customer table will filtered out by the “FALSE” (again being the result of NOT CROSSFILTERED). When the Baseline customer is not filtered the reverse will happen. A genius solution if you ask me (again full credits go to Srini).
3. Finally in returning the results, we use CALCULATE to change the filter context. By using ALL we overwrite any filters set directly on the dimension table and propagate our own filters that is getting returned from the variables.

Now we get the results we want, you can see below the new “SalesAmount 2” measure now is getting filtered by the baseline tables. The regular measure is not working as it is filtered by both dimensions:

That’s it. One thing to remember of course is that performance will not be as optimal as using real relationships, Marco and Alberto described this in a great blog post here. You can download the workbook here.

I am using DAX Formatter to show the DAX above: