Calling tables as variables in other variable

[jdriscoll]

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 Automate. Basically we have people do a survey of a self-evaluation on various skills. I am working on a measure wherein I was trying to build a summary table of the average scores for each Specific Skill/Industry across all respondents, then wanted to grab the minimum score and return the corresponding Specific Skill/Industry.  So I built a summary table as a variable in the measure, but then I can't seem to get other variables within the measure to reference this summary table. Here is what I would like to do, but I get errors with it:

Min_Skill = 
VAR Avg_Table =
    SUMMARIZE (
        Survey_Table,
        Survey_Table[Specific Skill/Industry],
        "Avg_Score", AVERAGEX ( Survey_Table, [Latest_Score] )
    )
VAR Min_Val =
    CALCULATE ( MIN ( Avg_Score ), ALL ( Avg_Table ) )
RETURN
    CALCULATE (
        MIN ( Avg_Table[Specific Skill/Industry] ),
        Avg_Table[Avg_Score] = Min_Val
    )

(Latest_Score is a measure I created to capture only the most recent survey responses for each person, allowing for a person to respond multiple times to the survey. For the purpose of this question, you can treat this measure and the original "Experience Level" column the same.)

The measure doesn't seem to recognize the internal links to the Avg_Table or to the Avg_Score summary column within Avg_Table. Is there any way to accomplish this without building a whole separate calculated table?

[daXtreme · Accepted solution]

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

[jdriscoll]

Thanks a bunch, worked like a charm.