Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
TungNguyen_19
Helper III
Helper III

What is the purpose of this code block RETURN below?

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:

TungNguyen_19_0-1706861501667.png

 

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:

TungNguyen_19_1-1706861695085.png

 

Many thanks for any help

1 REPLY 1
123abc
Community Champion
Community Champion

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:

  1. 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.
  2. 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.
  3. 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.
  4. 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.
  5. 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.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors