GENERATESERIES Bug?

[Greg_Deckler]

Steps to reproduce:

1. Open Attached PBIX file (DeliveryDates2.pbix). Note FIFO Measure returns 4 for item 1507

2. In the table visual on the left, add FIFO Measure 2 to the table.

3. Can't display the visual with details being: Calculation error in measure 'BackOrders'[FIFO Measure 2]: The arguments in GenerateSeries function 

Now, the FIFO Measure and FIFO Measure 2 are exactly the same except FIFO Measure currently returns the Length of the PATH created. This clearly shows that the __Length variable is NOT blank. Since the other parameters to GENERATESERIES are hard-coded, this can be the only parameter that GENERATESERIES thinks is blank. But it isn't. Now, if you remove the relationships in the model, the measure works just fine and does not return this error, which doesn't make a lot of sense. Also, if you use independent slicers (DeliveryDates.pbix) the measures work just fine also. Also, if no item is chosen in the slicer (All), the FIFO Measure 2 measure returns just fine.

Detailed error message:

Feedback Type:
Frown (Error)

Timestamp:
2022-02-03T00:17:09.9123010Z

Local Time:
2022-02-02T19:17:09.9123010-05:00

Session ID:
02b5d6d4-2b13-4964-aa20-fd993813e8f6

Release:
December 2021

Product Version:
2.100.1401.0 (21.12) (x64)

Error Message:
MdxScript(Model) (187, 21) Calculation error in measure 'BackOrders'[FIFO Measure 2]: The arguments in GenerateSeries function cannot be blank.

OS Version:
Microsoft Windows NT 10.0.19042.0 (x64 en-US)

CLR Version:
4.7 or later [Release Number = 528372]

Peak Virtual Memory:
103 GB

Private Memory:
1.5 GB

Peak Working Set:
1.15 GB

IE Version:
11.789.19041.0

User ID:
9cb4abc4-9629-41d7-bb4d-1c7c6af7bcb3

Workbook Package Info:
1* - en-US, Query Groups: 0, fastCombine: Disabled, runBackgroundAnalysis: True.

Telemetry Enabled:
True

Snapshot Trace Logs:
C:\Users\gdeckler\Microsoft\Power BI Desktop Store App\FrownSnapShot3f4b6708-bd86-46cf-bec0-f17994c15278.zip

Model Default Mode:
Import

Model Version:
PowerBI_V3

Performance Trace Logs:
C:\Users\gdeckler\Microsoft\Power BI Desktop Store App\PerformanceTraces.zip

Disabled Preview Features:
PBI_shapeMapVisualEnabled
PBI_SpanishLinguisticsEnabled
PBI_qnaLiveConnect
PBI_azureMapVisual
PBI_dataPointLassoSelect
PBI_compositeModelsOverAS
PBI_dynamicParameters
PBI_enhancedTooltips
PBI_enableWebView2
PQ_WebView2Connector
PBI_useModernFormatPane
PBI_sparklines
PBI_scorecardVisual

Disabled DirectQuery Options:
TreatHanaAsRelationalSource

Cloud:
GlobalCloud

DPI Scale:
200%

Supported Services:
Power BI

Formulas:

section Section1;

shared OpenPurchaseOrders = let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pdZLbgMhDADQq0RZVxrbmN8N2l2lLqve/xoFZgAbhmSUrBIlebGNDczv791ugBsB0f3jTgY8+PTm5/Pr20SI6S2m70J6lb+zHOH+9/EaRnLFusGWN8WGUCxEk79iaSMAvKzJvqMxhkPrPzUQuPMY9rKDHb19j1PNncIGPn+K+VOK6KAtuslZoy1twA2oYodkC8a4QRCYUXTMithBaKyhX9KhJW4H7eOhGZBL4uTLv7Dgka7rgKNOa/FO8NDGJQrrAPuKW2ntSeJLnAKTzHsDM6V9im+rkNa452wPJpkhPvrLg/M05bpHFblym44LeqrUwJFyzkjp0Mc6Yt+T6GTBVNsLWntDteLjCCJZMSKlph8jjSBgdPQ8bN4QsOB+bJCEDHzB7YskXZrXo06UzqQ0YnX5eMkvcnke7tub2LDoRU/aIft8VXO4PrUp91V5Dfbl7BMUlqvJY57SuWUX/LzHTuZ21YsTPc1t60jCKBqC5UCQjYxyhUJcKFeVsehGxXyOxpmJKkE8yguakeVeXrnsrPOlJ1FOqnukb5IFtaj1tFXKoNH9z7uxp9qvxtFRi7aPt3JYd7+BgTmrw6UfCNZ27+T8eLwqmGbwgiudUK6dcVN5fffW/ZTq4xavzN+l9cxnL19ZTxg3lAwYgFZunGrlDCzjucGRcuQvuNII5cgv82yuzwvP87IhCcXTrUpCiYdGrUSO5YFLh7L9VlTMGtAMjXQ+wYWzujTlWgeS0Y+2rtV2TEr6gW3MrV2bsDopEoobcIJ+qE+6drXk7BULMJfXw7nnrB7xyrUL6ZHbB1o6s05zepKSLuaN/vcP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Document Number" = _t, #"Shipment Header" = _t, #"Item Code" = _t, #"ETA W/H" = _t, Quantity = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}, {"Document Number", Int64.Type}, {"Shipment Header", type text}, {"Item Code", Int64.Type}, {"ETA W/H", type text}, {"Quantity", Int64.Type}}),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Date", type date}}, "en-GB"),
#"Changed Type with Locale1" = Table.TransformColumnTypes(#"Changed Type with Locale", {{"ETA W/H", type date}}, "en-GB")
in
#"Changed Type with Locale1";

shared BackOrders = let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZVLbsMwDETvEqC7oOZHEslTdNFlkPtfo7JkS5Tl1u4iEEInDzNDin69HrIgLQSEj+fDIiXI5/cXAIMGDPkLgnE+BGB9VD6fAB976f185Z8MEJbYIYm1Q4JOkFJaITRConUlBuQgaWKk3xDmEEwdMctoKnBEBHQI8VZqHlRRsnOQNlBcAFcOFU4CJ4UKYOPwnCu3XG2ASOy5mlC6l+sEEQ+RW7niiFAXitJNM2uy0JINCahBEiPV7GqfZzeo55Ro6igEjhJMUzmkNr6hQgWFBbE3GiI3kCgHD5qirVJ07DB2JcZq3k+OQI+GSu0EQ+Qw5g1xnMON/4lF1/FBKxSUYsto15PumyocNpzUrKWbaiKsk0fVEuYzH7FforiBbJwZcTOTWDoo/+MYcK6cItghAnVE3VqDH6mIPCduVIJS30yCKQx2Dohm5Dht/RKK1pu8GZmbjK3J1yNbITNjR4zbjaBfZMU6qy3PScbuhUcGuzfGttm2QP9aBteb+lrIYcWie2GQ3WRcz/swqYfeZsb7Bw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"PO#" = _t, #"Sales Order #" = _t, #"Item Code" = _t, #"Ordered Qty" = _t, #"Invoiced Qty" = _t, #"% Delivered" = _t, #"Backorder Qty" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}, {"PO#", Int64.Type}, {"Sales Order #", type text}, {"Item Code", Int64.Type}, {"Ordered Qty", Int64.Type}, {"Invoiced Qty", Int64.Type}, {"% Delivered", Percentage.Type}, {"Backorder Qty", Int64.Type}}),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Date", type date}}, "en-GB")
in
#"Changed Type with Locale";

DeliveryDates2.pbix

DeliveryDates.pbix

[v-cazheng-msft]

Hi @Greg_Deckler 

Issue has been reported(ICM 287088549). It will be investigated. Will sync here once there is any update.

Best Regards,

Community Support Team _ Caiyun

[v-cazheng-msft]

Hi @Greg_Deckler ,

I’ve got feedback from product team. This behavior is by design. And here is the explanation.

define

measure 'BackOrders'[FIFO Measure 2] =

….(Other non-important variables)

    VAR __ItemCode = MAX('BackOrders'[Item Code])

RETURN

    countrows(GENERATESERIES(1, PATHLENGTH(CONCATENATEX(FILTER(ALL('OpenPurchaseOrders'),[Item Code] = __ItemCode), [ETA W/H] & ":" & [Quantity], "|")), 1))

EVALUATE

  SUMMARIZECOLUMNS(

      'BackOrders'[Sales Order #],

      'BackOrders'[Item Code],

      TREATAS({1507}, 'ItemCodes'[Item Code]),

      "FIFO_Measure_2", 'BackOrders'[FIFO Measure 3]

)

In your scenario, __ItemCode=Max(‘Back Orders[Item Code]) is not a constant, its value is dependent on the column ‘BackOrders[Sales Order#]’ and the filter TREATAS({1507},’ItemCode’[Item Code]).

Since ‘BackOrders’[Item Code] has other values such as 12038 in addition to 1507, MAX(‘BackOrders’[Item Code] returns BLANK when the value of ‘BackOrders’[Item Code] is not 1507. As a result, FILTER(ALL(‘OpenPurchageOrders’), [Item Code]=BLANK()) returns empty result set, which causes PATHLENGTH (CONCATENATEX()) to be blank, and finally fails the GENERATESERIES() function.

The reason why you didn’t see BLANK values when you changed the expression to return PATHLENGTH directly is that Power BI visuals by default don’t show rows with only BLANK measure values.

In case you wonder why filter TREATAS({1507},’Item Code)) doesn’t restrict the column ‘BackOrders’[Item Code] to 1507 only, it’s because filters only restrict grouping columns from the same table. Since the filter is from table ‘Item Code’ but the grouping column is from ‘BackOrders’, the filter doesn’t restrict the grouping column directly. That being said, both the filter and grouping columns apply to the measure expression, therefore, MAX(‘BackOrders’[Item Code]) is filtered by both hence returns BLANK when the value of ‘BackOrders’[Item Code] is different from the value of ‘Item Code’[Item Code]. This expression why when the relationship is deleted, the measure will work as the filter on ‘Item Code’[Item Code] no longer affects the value of MAX(‘BackOrders’[Item Code]). The illusion of filter restricting grouping columns is through the implicit filter of removing rows with all BLANK measure values.

Workaround:

Change the GENERAGESERIES expression by converting BLANK to 0 for the second argument: GENERATESERIES(1, COALESCE(PATHLENGTH(__SortedString), 0),1)

Best Regards,

Community Support Team _ Caiyun

[Greg_Deckler]

Thanks @v-cazheng-msft, makes sense.