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

问题描述

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

I have the following SSAS Tabular model defined:

在产品表上，我定义了以下度量:

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

1. DeliveryQty2018:=计算(SUM(PurchaseDelivery [PurchaseOrderQuantity])，((PurchaseEstimatedWarehouseArrivalDate [PurchaseEstimatedWarehouseArrivalYear] = 2018))
2. DeliveryQty2019:=计算(SUM(PurchaseDelivery [PurchaseOrderQuantity])，(PurchaseEstimatedWarehouseArrivalDate [PurchaseEstimatedWarehouseArrivalYear] = 2019))
3. 2018-2020年交付总数量:=格式([DeliveryQty2018] + [DeliveryQty2019] + [DeliveryQty2020]，#### ###")

我正在Power BI报表上创建一个可视表，该表包含以下字段:

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

这种组合为我提供了笛卡尔积:产品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将空格(空)转换为空字符串"" 而不是适当的空格，因此您可能要检查一下首先，然后再进行格式化.

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 visual上的笛卡尔积的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！