Forum Discussion

jdriscoll's avatar
jdriscoll
Icon for Helper I rankHelper I
4 years ago
Solved

Calling 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...
  • daXtreme's avatar
    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