What is the purpose of this code block RETURN below?

TungNguyen_19 — Fri, 02 Feb 2024 08:16:10 GMT

Hi everyone,

Today I was handled by my colleague her report! Unfortunately she was not here to explain some code for me and there is no documentation so I need some help to show me what does the code in RETURN block do?

@Table = VAR _toPathsTable = ADDCOLUMNS ( SELECTCOLUMNS ( '_G Vendors', "VendorID", '_G Vendors'[USERNAME], "@RFNo", SUBSTITUTE ( '_G Vendors'[POSITION], ",", "|" ) ), "@Length", PATHLENGTH ( [@RFNo] ) ) VAR T = ADDCOLUMNS ( _toPathsTable, "@Cumulative", SUMX ( FILTER ( _toPathsTable, [VendorID] <= EARLIER ( [VendorID] ) ), [@Length] ) ) RETURN ADDCOLUMNS ( SELECTCOLUMNS ( ADDCOLUMNS ( GENERATESERIES ( 1, SUMX ( T, [@Length] ) ), "Cumulative", MINX ( FILTER ( T, [@Cumulative] >= [Value] ), [@Cumulative] ) ), "VendorID", MAXX ( FILTER ( T, [@Cumulative] = [Cumulative] ), [VendorID] ), "RF Combined", MAXX ( FILTER ( T, [@Cumulative] = [Cumulative] ), [@RFNo] ), "RF Count", 1 + [Cumulative] - [Value] ), "RFNo", TRIM(PATHITEM( [RF Combined], [RF Count] )) )

- Here are some snippet data for each temporary table:

_toPathsTable returned data:

The T table is added Cumulative column base on VendorID

But the RETURN block I have no idea why she use GENERATESERIES function also the "RF Combined" and "RF Count" are disapeared from the returned data:

Many thanks for any help

Re: What is the purpose of this code block RETURN below?

123abc — Fri, 02 Feb 2024 09:48:52 GMT

The purpose of the RETURN block in the provided DAX (Data Analysis Expressions) code is to generate a new table as the output of the entire DAX query. Let's break down the RETURN block and its purpose:

GENERATESERIES Function:
- The GENERATESERIES function generates a table with a single column containing sequential numbers.
- In this context, it seems to be used to generate a series of numbers from 1 to the total length of the cumulative path lengths calculated earlier in the query.
Transformation and Filtering:
- The MINX function inside the GENERATESERIES column computes the cumulative path lengths until the current row, and the MINX function retrieves the minimum cumulative path length that is greater than or equal to the current row's value.
- This part seems to be constructing a new table where each row represents a specific point in the cumulative path lengths.
SELECTCOLUMNS and ADDCOLUMNS:
- The SELECTCOLUMNS function is used to select specific columns from an existing table or transform columns.
- The ADDCOLUMNS function is used to add new calculated columns to a table.
Transformation of RF Combined and RF Count:
- The "RF Combined" and "RF Count" columns are indeed present in the final output, but they are being transformed in a way that the "RF Combined" values are filtered based on the cumulative counts, and "RF Count" seems to be recalculated.
Final Output:
- The final output of the RETURN block is a new table that comprises columns such as "VendorID", "RF Combined", and "RF Count" based on the calculations and transformations applied within the block.

In summary, the RETURN block is constructing a new table based on the previously defined tables and calculations. It seems to be transforming the cumulative path lengths and combining them with specific vendor IDs and RF numbers, resulting in a structured output.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

topic What is the purpose of this code block RETURN below? in DAX Commands and Tips

What is the purpose of this code block RETURN below?

Re: What is the purpose of this code block RETURN below?