DAX - Power Automate Power BI Connector: Run a Query Against a Dataset

[GTPowerBIUser]

I'm posting here because I suspect the issue is with my DAX - SUMMARIZECOLUMNS()

I'm getting a strange error: 

{"error":{"code":"DatasetExecuteQueriesError","pbi.error":{"code":"DatasetExecuteQueriesError","parameters":{},"details":[{"code":"DetailsMessage","detail":{"type":1,"value":"Query (18, 3) A single value for column 'Location' in table 'PS_Org_Emp' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."}},{"code":"AnalysisServicesErrorCode","detail":{"type":1,"value":"3241803789"}}]}}}

In power bi I have a relationship set, so it only provides a single value. Location is not a calculation, it is simply the Location however I think despite having the relationship in the dashboard - its not showing here. How do I create that relationship in SummarizeColumns between PS_Emp and PS_Org_EMp? See how I'm doing it below... probably not correct

This is my query:

EVALUATE SUMMARIZECOLUMNS(
PS_Emp[Name], "Name",
PS_Emp[USID], "USID",
PS_Emp[Hire Date], "Hire Date",
PS_Emp[Rank], "Rank",
PS_Emp[Title], "Title",
PS_Emp[FlexTrack], "FlexTrack",
CALCULATE(sum(PS_Emp[Work Hours in Year])/2080), "FTE",
PS_Org_Emp[Market], "Market",
PS_Org_Emp[LLC LLP], "LLC LLP",
PS_Emp[Specialty], "Specialty",
PS_Emp[ECS ICS], "ECS ICS",
PS_Emp[Coach], "Coach",
PS_Emp[Solution Family], "Solution Family",
PS_Emp[SL], "Sub Solution Family",
PS_Emp[NonDCBased], "NonDCBased",
PS_Emp[CTL], "CTL",
PS_Org_Emp[Location], "Location",

FILTER(PS_Emp, PS_Emp[Status] = "Active" &&
PS_Emp[Title] <> "Partner" &&
PS_Emp[Employee Class] <> "BRWDRESORCE" &&
PS_Emp[Employee Class] <> "SUBCONTRACT")
)

In the power bi data, PS_Emp relates to PS_Org_Emp based on a one to many join: 

PS_Emp: 

Employee ID | Organization ID

98765 | 1.1.15

67890 | 1.1.10

34567 | 1.1.15

PS_Org_Emp

Organization ID | Location | LLC LLP | Market

1.1.15  |  Baltimore, MD | LLC | Ag

1.1.10 | Boston, MA | LLC | Cereal

[lbendlin]

Your sample data is missing the filter columns and the aggregated fact column.  Based on the limited data you provided this query will work:

EVALUATE SUMMARIZECOLUMNS(
PS_Emp[Employee ID],
PS_Org_Emp[Market], 
PS_Org_Emp[LLC LLP], 
PS_Org_Emp[Location]
)

Note that the syntax for SUMMARIZECOLUMNS() is slightly different than what you tried to use.

Please provide sanitized sample data that fully covers your issue.

Please show the expected outcome based on the sample data you provided.

[GTPowerBIUser · Accepted solution]

I ws able to solve this by running the Perf Analyzer and coping the code for the visual that needed to be recreated. Much simpler then I had originally thought. 

[VuongLM93]

I have also an issue - does this flow not work for Live connection.  (dataset from someone in the organization created, I just have the live connection )