DAX FORMAT 函数导致 Power BI 视觉对象上的笛卡尔积 [英] DAX FORMAT function cause cartesian product on Power BI visual

问题描述

我定义了以下 SSAS 表格模型:

I have the following SSAS Tabular model defined:

在 Product 表中，我定义了以下度量:

On the Product table, I have the following measures defined:

1. DeliveryQty2018:= CALCULATE (SUM (PurchaseDelivery[PurchaseOrderQuantity]), (PurchaseEstimatedWarehouseArrivalDate[PurchaseEstimatedWarehouseArrivalYear] = 2018))
2. DeliveryQty2019:= CALCULATE (SUM (PurchaseDelivery[PurchaseOrderQuantity]), (PurchaseEstimatedWarehouseArrivalDate[PurchaseEstimatedWarehouseArrivalYear] = 2019))
3. Sum DeliveryQty 2018-2020: = FORMAT([DeliveryQty2018] + [DeliveryQty2019] + [DeliveryQty2020],"# ### ###")

我正在我的 Power BI 报表上创建一个表格视觉对象，其中包含以下字段:

I'm creating a table visual on my Power BI report, that consists of the following fields:

这个组合给了我一个笛卡尔积:Product X ProductCategory:

This combinations gives me a cartesian product of: Product X ProductCategory:

有趣的是，当我删除 Sum DeliveryQty 18-20 的 FORMAT() 包装器时，笛卡尔积被删除，我获得了我正在寻找的单一记录.但是，如果我删除 ProductCategory 字段并使用 FORMAT() 函数保留 Sum DeliveryQty 18-20 度量，我也会得到单条记录..

What's interesting, when I remove the FORMAT() wrapper for the Sum DeliveryQty 18-20, cartesian product is removed and I achieve the single record I was loooking for. However, if I remove the ProductCategory field and leave the Sum DeliveryQty 18-20 measure with the FORMAT() function in place I also get the single record..

谁能向我解释这两种情况下发生了什么?

Can anyone explain to me what's going here in both scenarios?

推荐答案

FORMAT 将空白(nulls)变成空字符串 "" 而不是正确的空白，所以你可能想检查一下首先在格式化之前.

FORMAT turns blanks (nulls) into empty strings "" rather than proper blanks, so you probably want to check for that first before formatting.

Sum DeliveryQty 2018-2020: =
VAR Qty = [DeliveryQty2018] + [DeliveryQty2019] + [DeliveryQty2020]
RETURN
    IF ( ISBLANK ( Qty ), BLANK(), FORMAT ( Qty, "# ### ###" ) )

这篇关于DAX FORMAT 函数导致 Power BI 视觉对象上的笛卡尔积的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！