Power BI Slicer过滤无法正常工作 [英] Power BI slicer filtering not workin

问题描述

这是我过去

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

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

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

构建衡量指标的目的是，如果衡量指标评估为空白，则向分析人员显示所有属于特定类别的产品的显式价值.

不幸的是，我发现改写自然"字样会引起误解.度量中的空白可能会对表格中显示的数据产生负面影响:使用切片器进行过滤无法正常工作-当我选择特定的产品类别(例如"Office")时，我得到了笛卡尔产品此类别以及所有SKU(也在过滤类别之外)

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

大多数基于 ProductSku 级别的运营报告具有相似的视觉设置，我真的很希望支持使用某些技术值来格式化空白度量，这些技术值仍然允许建立关系可以正常工作，而不会产生奇怪的影响，例如笛卡尔乘积或来自切片器等其他视觉效果的令人讨厌的滤镜

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

EDIT1

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

解决方案

用特定值替换空格绝对可以导致这种情况.结果可能是空白的，因为事实表中没有相应的数据行；或者结果可能是空白的，因为在维表中甚至没有这种组合，并且您不能仅仅从空白中分辨出哪一个，因此替换空白将适用于这两种情况，或者两种情况都不适用.

我们想忽略不可能的组合.正如@sergiom正确指出的那样，它们是由于 auto-exist 无法插入，因为类别和SKU在同一表中不存在.因为它们在不同的表中，所以内部逻辑使用交叉连接和筛选的更强力的方法.但是，您已经用其他内容代替空白来干扰过滤部分.

如果无法创建更简洁的模型，解决此问题的方法是在评估度量之前检查空的交叉联接.

例如，代替

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

您可能要写一些额外的支票:

  IF(ISEMPTY(产品)，空白的 ()，IF(ISBLANK([措施])；无库存"； [措施])) 

通过这种方式，您仅可以评估实际有意义的案例的度量.

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 Slicer过滤无法正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！