Power BI 切片器过滤不起作用 | 切片器过滤不起作用

本文介绍了Power BI 切片器过滤不起作用的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

这是我过去开始的主题的延续

基于模型，我构建了以下报告:

如您所见，在视觉上我结合了 ProductCategory 和 Product 表中的属性.我还添加了一个度量，这里命名为 [Some Measure]，其定义如下:

IF (ItemStockHistory[#ItemStockCurrent] <= 0;没有库存"；除 (ItemStockHistory[#ItemStockCurrent];[Σ SalesUnitQuantity_Last30Days]))

这种度量构造的目标是向分析师显示属于特定类别的所有产品的明确价值，以防度量评估为空白.

不幸的是，我发现覆盖自然"；度量中的空白可能会对表格视觉中显示的数据产生副作用:使用切片器进行过滤无法正常工作 - 当我选择特定产品类别(如Office")时，我得到笛卡尔积此类别和所有 SKU(也包括过滤类别之外的这些)

对我来说，这是表格建模的非常令人惊讶的行为.为什么用显式值覆盖度量 BLANK 结果会影响过滤?

大多数基于 ProductSku 级别的运营报告共享类似的视觉设置，我真的希望支持格式化空白度量以及一些仍然允许建立的技术值关系正常工作，没有奇怪的效果，如笛卡尔积或来自其他视觉效果的 ingoring 过滤器，如切片器

或者也许我不了解表格建模的主要范式，并且想了解该技术默认禁止的内容?

EDIT1

缺少的 ItemStockHistory 表已添加到数据模型图中

解决方案

用特定值替换空白肯定会导致这种事情.结果可能是空白的，因为您的事实表中没有相应的数据行，或者它可能是空白的，因为在维度表中甚至不可能进行这种组合，并且您不能仅通过查看空白来判断其中哪些，因此替换空白将适用于这两种情况或都不适用.

我们想忽略不可能的组合.正如@sergiom 正确指出的那样，它们是由于 auto-exist 因为类别和 SKU 不存在于同一个表中，所以没有启动.因为它们在不同的表中，所以内部逻辑使用了交叉连接和向下过滤的更暴力的方法.但是，您通过用其他东西替换空白来干扰过滤部分.

如果您无法创建更清晰的模型，解决此问题的方法是在评估度量之前检查是否存在空交叉连接.

例如，而不是

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

你可能会写一些带有额外检查的东西:

IF (ISEMPTY(产品)，空白的 ()，IF (ISBLANK ([Measure]);No Stock";[Measure]))

这样，您只评估真正有意义的案例的度量.

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

解决方案

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 切片器过滤不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！