Forum Discussion
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...
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 t6This gives me the correct output. But thanks very much for your codes. Really appreciate it.
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.
