Power BI slicer filtering not workin

Question

This is a continuation of a thread I started in a past here

After some time I'm coming back with kind of similar question, but this time would like to get the full understanding on the problem to get it solved finally.

Let's say I use the following Power BI data model:

Based on the model, I have build the following report:

As you can see, on the visual I combine attributes from ProductCategory and Product tables. I'm also adding a measure, here named [Some Measure], that is defined like:

IF (
    ItemStockHistory[# ItemStockCurrent] <= 0;
    "No Stock";
        DIVIDE (
            ItemStockHistory[# ItemStockCurrent];
            [Σ SalesUnitQuantity_Last30Days]
    )
)

The goal of such measure construction is to show explicit value for ALL products belonging to the specific category, to the analyst, in case measure evaluates to BLANK.

Unfortunately, I discovered that overwriting "natural" blanks in the measure might have a side effects on the data displayed in the table visual: filtering using slicer is not working properly - when I select specific product category like "Office", I get the cartesian product of this category and all SKUs (also these outside of filtered category)

For me this is quite suprising behaviour of tabular modelling. Why overwriting measure BLANK result with explicit value affects filtering?

Most of the operational reports, that are based on the ProductSku level, share the similar visual setup and I really would like to have support for formatting blanks measure with some technical values that would still allow established relationships to work properly, without the strange effects, like the cartesian products or ingoring filters coming from the other visuals, like slicers

Or maybe I do not understand tabular modelling prime paradigms and want to get what is prohibited by default in this technology?

EDIT1

Missing ItemStockHistory table was added to the data model diagram

what is "ItemStockHistory"? It's not in the data model you described — RADO

Alexis Olson Alexis Olson · Accepted Answer · 2021-01-20T18:35:10

Replacing blanks with specific values can definitely lead to this sort of thing. A result can be blank because there are no corresponding data rows in your fact table or it can be blank because that combination isn't even possible in the dimension tables and you can't tell just from looking at a blank which of these which, so replacing a blank will apply to both or neither of these cases.

We'd like to ignore the impossible combinations. As @sergiom correctly points out, they occur as a result of auto-exist not kicking in because category and SKU don't exist in the same table. Because they are in different tables, the internal logic uses the more brute-force approach of cross-joining and filtering down. However, you've interfered with the filtering down part by replacing blanks with something else.

The way to get around this if you can't create a cleaner model is to check for an empty cross-join before evaluating the measure.

For example, instead of

IF ( ISBLANK ( [Measure] ); "No Stock"; [Measure] )

You might write something with an extra check:

IF (
    ISEMPTY ( Product ),
    BLANK (),
    IF ( ISBLANK ( [Measure] ); "No Stock"; [Measure] )
)

This way, you only evaluate the measure for cases that actually make sense.

Power BI slicer filtering not workin

2 Answers