Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Is there any alternative of NATURALLEFTOUTERJOIN on a temp table ... if yes, want to test out the performnce imapct with the alternative. ANy help is appreciated !!!
LOOKUPVALUE won't work on a temp table.
E.g. Query
Table =
VAR _1 = DATATABLE (
"ID", STRING,
"BU", STRING,
"YEAR", INTEGER,
"Month", INTEGER,
{
{ "BU1-2019-1", "BU-1", 2019, 1 },
{ "BU1-2019-2", "BU-1", 2019, 2 },
{ "BU1-2019-3", "BU-1", 2019, 3 }
}
)
VAR _2 = DATATABLE (
"ID", STRING,
"Actual", INTEGER,
{
{ "BU1-2019-1", 100 },
{ "BU1-2019-2", 200 },
{ "BU1-2019-3", 300 }
}
)
VAR _3 = NATURALLEFTOUTERJOIN(_1,_2)
RETURN _3
Hi @smpa01
Kindly check below one:
Table 3 =
VAR _1 =
DATATABLE (
"ID", STRING,
"BU", STRING,
"YEAR", INTEGER,
"Month", INTEGER,
{
{ "BU1-2019-1", "BU-1", 2019, 1 },
{ "BU1-2019-2", "BU-1", 2019, 2 },
{ "BU1-2019-3", "BU-1", 2019, 3 }
}
)
VAR _2 =
DATATABLE (
"ID", STRING,
"Actual", INTEGER,
{
{ "BU1-2019-1", 100 },
{ "BU1-2019-2", 200 },
{ "BU1-2019-3", 300 }
}
)
VAR _3 =
SELECTCOLUMNS ( _2, "ID", [ID] )
RETURN
ADDCOLUMNS (
FILTER ( _1, [ID] IN _3 ),
"Actual", VAR i = [ID] RETURN SUMX ( FILTER ( _2, [ID] = i ), [Actual] )
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |