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 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?
As mentioned, I need a DAX based solution, not in Power Query.
I assume that the 2 variables (VAR_SourceData, VAR_ReferenceYearLookup) are tables, and have this code below:
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