Forum Discussion

snph1777's avatar
snph1777
Helper V
4 years ago
Solved

Microsoft Power BI - DAX - develop a dataset using variables - NATURALJOIN, GENERATE, GENERATEALL

I have a Power BI Desktop file. I am developing a Calculated Table (CT) in DAX language.   I am using a number of manipulations inside to develop this CT (similar to what I do in a T-SQL stored pro...
Need Help
  • snph1777's avatar
    snph1777
    4 years ago

    Hello Ben,

     

    I referred to your solution, and zeroed-in on the logic.

     

    I assume that the 2 variables (VAR_SourceData, VAR_ReferenceYearLookup) are tables, and have this code below for a Calculated Table:

     

     

     

    DesiredOutput_CT = 

VAR src=DISTINCT(

                    SELECTCOLUMNS(

                                   VAR_SourceData,

                                   "City", [City],
                                   "Product", [Product],
                                   "Quantity", [Quantity]

                                  )

                   )

VAR cj = CROSSJOIN(src, VAR_ReferenceYearLookup)

VAR t1 = SELECTCOLUMNS(

                        cj,

                        "Concat", [City] & "-" & [Product] & "-" & [Year_LKP],
                        "City", [City],
                        "Product", [Product],
                        "Quantity", [Quantity],
                        "Year", [Year_LKP]

                      )

VAR t2 = SELECTCOLUMNS(

                        VAR_SourceData,

                        "Concat", [City] & "-" & [Product] & "-" & [Year],
                        "Price", [Price]

                      )

VAR t3 = SELECTCOLUMNS(

                        t1,

                        "Concat", [Concat] & "Z",
                        "City", [City],
                        "Product", [Product],
                        "Quantity", [Quantity],
                        "Year", [Year]

                      )

VAR t4 = SELECTCOLUMNS(

                        t2,

                        "Concat", [Concat] & "Z",
                        "Price", [Price]

                      )

VAR t5 = NATURALLEFTOUTERJOIN(t3,t4)

VAR t6 = SELECTCOLUMNS(

                        t5,

                        "City", [City],
                        "Product", [Product],
                        "Quantity", [Quantity],
                        "Price", [Price],
                        "Year", [Year]

                      )

RETURN t6

     

     

     

    This gives me the correct output. But thanks very much for your codes. Really appreciate it.

bcdobbs's avatar
bcdobbs
Community Champion
4 years ago

Sorry slightly misunderstood your requirement.

 

How about this:

EVALUATE
VAR CityProductList =
    SUMMARIZE ( VAR_SourceData, VAR_SourceData[City], VAR_SourceData[Product] )
VAR YearList =
    VALUES ( VAR_ReferenceYearLookup[Year_LKP] )
VAR CombinationTable =
    CROSSJOIN ( CityProductList, YearList )
VAR Result =
    ADDCOLUMNS (
        CombinationTable,
        "Price",
            VAR CurrentCity = VAR_SourceData[City]
            VAR CurrentProduct = VAR_SourceData[Product]
            VAR CurrentYear = VAR_ReferenceYearLookup[Year_LKP]
            RETURN
                LOOKUPVALUE (
                    VAR_SourceData[Price],
                    VAR_SourceData[Product], CurrentProduct,
                    VAR_SourceData[City], CurrentCity,
                    VAR_SourceData[Year], CurrentYear
                ),
        "Quantity",
            VAR CurrentProduct = VAR_SourceData[Product]
            RETURN
                LOOKUPVALUE (
                    VAR_SourceData[Quantity],
                    VAR_SourceData[Product], CurrentProduct
                )
    )
RETURN
    Result

 

bcdobbs's avatar
bcdobbs
Community Champion
4 years ago

I personally like the following version better but both give the same results:

 

VAR CityProductList =
    SUMMARIZE (
        VAR_SourceData,
        VAR_SourceData[City],
        VAR_SourceData[Product]
    )
VAR YearList =
    VALUES ( VAR_ReferenceYearLookup[Year_LKP] )
VAR CombinationTable =
    CROSSJOIN (
        CityProductList,
        YearList
    )
VAR Result =
    ADDCOLUMNS (
        CombinationTable,
        "Price",
            VAR RowYear = VAR_ReferenceYearLookup[Year_LKP]
            RETURN
                CALCULATE (
                    MAX ( VAR_SourceData[Price] ),
                    VAR_SourceData[Year] = RowYear
                ),
        "Quantity",
            CALCULATE (
                MAX ( VAR_SourceData[Quantity] )
            )
    )
RETURN
    Result