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:image

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:image

The model now looks like this:

image

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 Smile (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:

image

 

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:
DAX Formatter by SQLBI

One Reply to “Dynamic relationships based on the selection”

  1. An alternative solution to this issue would be to make the relationships inactive and USERELATIONSHIP and IF(ISCROSSFILTERED. This would have two advantages. 1 it uses physical relationships so is more efficient and 2 as I understand it IF(ISCROSSFILTED is one of the very few cases where if the first part is true it will stop calculating at this point and not calculate the second part of the calculation. See here for my alternative solution. – https://1drv.ms/f/s!AvYLqlToxcVwvT2JUbLDF0L33b1C

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.