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

Question

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 procedure). I have the source as an excel file here.

I develop a number of variables within this DAX query. Ignore any relationships between the tables, since these are variables.

I have the below variable (a table) called VAR_SourceData. Please see the image file below:

It has 4 cities - NYC, LON, LA, SYD. It has 5 different combinations of City-Product.

Each City may have one or more Products.

A combination of a City-Product will have only one unique Quantity (always), regardless of the year.

For example, SYD and PineApple has 12 as its Quantity always, on the other hand SYD and Grapes has a Quantity of 11 always. (This is regardless of the Year).

A combination of City-Product may or may not have Price for all the years. For example, LON-Orange has Price for 2020 and 2019 only, on the other hand LA-Mango has Price for 2019 and 2015 only.

I have a master table (VAR_ReferenceYearLookup) with all the possible years. It has 9 years (2022-2014), in descending order.

I need the desired output of the table VAR_Desired_Output:

I explain the VAR_Desired_Output table as follows:

The table would have every possible combination of City-Product, with a fixed Quantity. The City, Product, Quantity are independent of the Year. However, the Price depends on the Year. If there is no data for Price in a year in the VAR_SourceData table, the VAR_Desired_Output table must show blank.

My desired output must have all the years (2022-2014 (in descending order, preferably)) for every possible combination of City-Product, with the fixed Quantity; the Quantity depends on the City-Product combination, but not on the Year. If the Price for a Year is not available in the VAR_SourceData table, the VAR_Desired_Output table must show blank.

Hence every City-Product combination must have exactly 9 years, with a fixed Quantity always.

I have 5 different combinations of City-Product, hence the VAR_Desired_Output table has 45 rows.

I tried with NATURALINNERJOIN, NATURALLEFTOUTERJOIN, GENERATE etc. But am not able to solve this. I need this as a DAX solution, NOT in Power Query (my table has a number of Calculated Columns).

Can anyone help me achieve my goal?

Answer 1

You can add some power query steps to your table VAR_SourceData to achieve the required output. Try to add these below steps in the Advance Query Editor window of your table-

Note: change the previous step name accordingly in the first below step

let
    //your previous existing steps,

    #"Added Custom" = Table.AddColumn(#"previous step name", "Custom", each List.Sort(
    List.Numbers(
        List.Min(VAR_ReferenceYearLookup[Year_LKP]),     
        List.Max(VAR_ReferenceYearLookup[Year_LKP]) 
        - List.Min(VAR_ReferenceYearLookup[Year_LKP]) + 1
    ), 
    Order.Descending
)),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Price_new", each if [Year]=[Custom] then [Price] else null),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Quantity_new", each if [Year] = [Custom] then [Quantity] else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Year", "Price", "Quantity"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Year"}, {"Price_new", "Price"}, {"Quantity_new", "Quantity"}}),
    #"Grouped Rows" = Table.Group(#"Renamed Columns", {"City", "Product", "Year"}, {{"Price", each List.Max([Price]), type nullable number}, {"Quantity", each List.Max([Quantity]), type nullable number}})
in
    #"Grouped Rows"

Output-