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

Hi,

If you upload and share a link from OneDrive /Drop Box/Google Drive etc I can have a go at this.

 

A few initial thoughts:

This looks like it would be straight forward in Power Query using Merge Tables and then a little work to lookup the static quantity.


I did wonder what your aim was with it though? You'd normally want to aim at a star schema. Can help with that but wasn't sure on the city interaction to product have same quantity /price regardless of city?

  • snph1777's avatar
    snph1777
    Helper V
    4 years ago

    Hello Ben,

     

    Thanks for your reply.

     

    https://docs.google.com/spreadsheets/d/1taBhqMkVA1GGBUg6EodsccMzlZfpVmOI/edit?usp=sharing&ouid=104970258887540074952&rtpof=true&sd=true

     

     

    https://drive.google.com/file/d/1INvkegkQFSD2VqTOR9huZulbaC5UiF4h/view?usp=sharing

     

    I have the links above:

     

    I need this as a DAX solution, not in Power Query. The model is has a lot of Calculated Columns, so obviously I cannot use Power Query.

     

    I put the situtaion as below:

     

    1) Assume that the there is a source table in Power BI Desktop:    VAR_SourceData

     

    2) There is a lookup table (not related to the source table): VAR_ReferenceYearLookup

     

    3) I need to develop a Calculated Table in DAX called VAR_Desired_Output

    • bcdobbs's avatar
      bcdobbs
      Community Champion
      4 years ago

      Try the code below.

       

      It's gets distinct lists of Products, Years and Cities and does a cross join on them.

       

      As part of the production of the year list I use TREATAS to tell it to treat the year column as the year from your SourceData table.

       

      Once crossjoined we can add new columsn with ADDCOLUMNS and just rely on data lineage (Understanding data lineage in DAX - SQLBI) and CALCULATE to force a context transition moving from row context to filter context in order to pass the row context to the original table. I use MAX simply to ensure I get a single value returned, SELECTEDVALUE would do the same.

       

      CalculatedOutput = 

VAR ProductList =
    VALUES ( VAR_SourceData[Product] )
    
VAR YearList =
    TREATAS (
        VALUES ( VAR_ReferenceYearLookup[Year_LKP] ),
        VAR_SourceData[Year] 
    )
    
VAR CityList =
    VALUES ( VAR_SourceData[City] )
    
VAR CombinationTable =
    CROSSJOIN ( ProductList, YearList, CityList )
    
VAR Result =
    ADDCOLUMNS (
        CombinationTable,
        "Price", CALCULATE ( MAX ( VAR_SourceData[Price] ) ),
        "Quantity",
            CALCULATE (
                MAX ( VAR_SourceData[Quantity] ),
                REMOVEFILTERS ( VAR_SourceData[Year], VAR_SourceData[City] )
            )
    )
    
RETURN
    Result

       

      • snph1777's avatar
        snph1777
        Helper V
        4 years ago

        Thanks Ben; appreciate your response; will get back.

         

        Can we use LOOKUPVALUE with variables to achieve this ?

         

        Also, can Quantity not be combined with the City-Product combination for the CROSSJOIN, since Quantity will be dependent on this pair only ?