Filtering on calculated column of Cartesian product in DAX

Question

I need a slicer for ticking only those Products and Regions which have budgeted Targets.

My data model is a bit complicated than I show here. In my real scenario table Budget does not exist and Target values have to be calculated from other tables of varying granularity. Lets assume we cannot use calculated column on Budget table.

Here green tables are one-column-all-values-dimension bridges. The red table is a Cartesian product of Products and Brands with calculated Target.

Here is a DAX code for the red table I cooked to solve the problem.

#Brand x Region =
ADDCOLUMNS (
    CROSSJOIN ( '#product', '#region' ),
    "Target", CALCULATE ( SUM ( Budget[target] ) ),
    "IsTarget", IF ( CALCULATE ( SUM ( Budget[target] ) ) > 0, "Yes", "No" )
)

The table shows like this:

But such cunningly obtained column IsTarget does not affect my visuals through the slicer. How to fix it.

File PBIX here.

Edit after comments. Alexis, is that what you mean? I added column P@R which is concatenation of Product and Region. It seems to work:-)

Is it possible in your real scenario to create a single product-region index column to create a relationship with instead of requiring a relationship for each? — Alexis Olson
So you propose Cartesian of Product x Brand as a bridge instead of single Product and single Brand. But how to connect such Cartesian to FactTable or other tables if there is only one column relation allowed? — Przemyslaw Remin
You'd have to add that index to the tables you want to make a relationship. That's why I was asking if that's possible in your case. It can certainly be done in your example but conflicts with your stated assumption on Budget. — Alexis Olson
Thank you. Can you have a look at the approach I suggested in my answer after your comments? — Przemyslaw Remin
This is pretty similar in concept and the concatenation is a nice shortcut instead of creating an integer index. If you hide the region and product tables to reduce confusion (just use the cross table instead), it's a decent solution. — Alexis Olson

Alexis Olson Alexis Olson · Accepted Answer · 2019-10-30T15:23:20

This is what I was suggesting, where the bottom relationships are on the Index columns.

In order to do this, my #Brand x Region table was this:

#Brand x Region = 
VAR CrossProduct =
    ADDCOLUMNS (
        CROSSJOIN ( '#product', '#region' ),
        "Target",
        CALCULATE (
            SUM ( Budget[target] ),
            FILTER (
                Budget,
                Budget[product] = EARLIER ( '#product'[product] ) &&
                Budget[region] = EARLIER ( '#region'[region] )
            )
        )
    )
RETURN
    ADDCOLUMNS(
        CrossProduct,
        "IsTarget", IF ( [Target] > 0, "Yes", "No" ),
        "Index", RANKX(CrossProduct, '#product'[product] & '#region'[region])
    )

(Note: The filtering has to be explicit since I'm not using the relationships you originally had.)

From there I pulled over the index to the FactTableSales and Budget with a lookup:

Index =
LOOKUPVALUE (
    '#Brand x Region'[Index],
    '#Brand x Region'[product], [product],
    '#Brand x Region'[region], [region]
)

Note that creating an index column is often easier in the query editor rather than trying to do it in DAX but you can't modify a calculated table in the query editor.

Filtering on calculated column of Cartesian product in DAX

1 Answers