Forum Discussion
jdriscoll
Helper I
4 years agoCalling tables as variables in other variable
Hi, I have a fact table with the following columns: "Completion Date", "Email", "Specific Skill/Industry", and "Experience Level". A Microsoft form feeds an Excel table in SharePoint via Power Autom...
- 4 years ago
Min_Skill = VAR Avg_Table = // SUMMARIZE should NEVER be used to create // aggregations. It can only ever be used // to create groupings of rows from an extended // table. This is the ONLY safe use of the function // which is flawed and will not be fixed, // as confirmed by Microsoft. But you don't need it // here at all. ADDCOLUMNS( DISTINCT( Survey_Table[Specific Skill/Industry] ), "@Avg_Score", CALCULATE( AVERAGEX( Survey_Table, [Latest_Score] ) ) ) VAR Min_Val_Skill = MINX( topn( 1, Avg_Table, [@Avg_Score], ASC ), Survey_Table[Specific Skill/Industry] ) return Min_Val_Skill
daXtreme
Solution Sage
4 years agoMin_Skill =
VAR Avg_Table =
// SUMMARIZE should NEVER be used to create
// aggregations. It can only ever be used
// to create groupings of rows from an extended
// table. This is the ONLY safe use of the function
// which is flawed and will not be fixed,
// as confirmed by Microsoft. But you don't need it
// here at all.
ADDCOLUMNS(
DISTINCT( Survey_Table[Specific Skill/Industry] ),
"@Avg_Score",
CALCULATE(
AVERAGEX(
Survey_Table,
[Latest_Score]
)
)
)
VAR Min_Val_Skill =
MINX(
topn(
1,
Avg_Table,
[@Avg_Score], ASC
),
Survey_Table[Specific Skill/Industry]
)
return
Min_Val_Skilljdriscoll
Helper I
4 years agoThanks a bunch, worked like a charm.