Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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";
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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