DAX FORMAT function cause cartesian product on Power BI visual

0
votes

I have the following SSAS Tabular model defined:

enter image description here

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],"# ### ###")

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

enter image description here

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

enter image description here

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?

1
powerbidax

1 Answers

1
votes

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, "# ### ###" ) )