Run a query against a dataset syntax error

[Anonymous_10]

Hi Team,

I needed to export data from power bi semantic model to a SP list. So I tried to use Run a query against a dataset actio n but I keep getting error.I am trying to get Power Automate to generate an output from a query against a Power BI dataset. With a test dataset containing a table named User email (Export), I have tried:

/ DAX Query

DEFINE

VAR __DS0Core = 

SUMMARIZECOLUMNS(

ROLLUPADDISSUBTOTAL(

ROLLUPGROUP('User email (Export)'[User email], 'User email (Export)'[Domain]), "IsGrandTotalRowTotal"

),

"SumWith_activities", CALCULATE(SUM('User email (Export)'[With activities])),

"SumOnly_view_activities_in_premium_workspace", CALCULATE(SUM('User email (Export)'[Only view activities in premium workspace])),

"SumAssigned_to_workspace", CALCULATE(SUM('User email (Export)'[Assigned to workspace])),

"SumAccount_enabled", CALCULATE(SUM('User email (Export)'[Account enabled]))

)

EVALUATE

__DS0Core

ORDER BY

[IsGrandTotalRowTotal] DESC,

'User email (Export)'[User email],

'User email (Export)'[Domain]

But the Actual Query was:

// DAX Query

DEFINE

VAR __DS0Core = 

SUMMARIZECOLUMNS(

ROLLUPADDISSUBTOTAL(

ROLLUPGROUP('User email (Export)'[User email], 'User email (Export)'[Domain]), "IsGrandTotalRowTotal"

),

"SumWith_activities", CALCULATE(SUM('User email (Export)'[With activities])),

"SumOnly_view_activities_in_premium_workspace", CALCULATE(SUM('User email (Export)'[Only view activities in premium workspace])),

"SumAssigned_to_workspace", CALCULATE(SUM('User email (Export)'[Assigned to workspace])),

"SumAccount_enabled", CALCULATE(SUM('User email (Export)'[Account enabled]))

)

VAR __DS0PrimaryWindowed = 

TOPN(

502,

__DS0Core,

[IsGrandTotalRowTotal],

0,

'User email (Export)'[User email],

1,

'User email (Export)'[Domain],

1

)

EVALUATE

__DS0PrimaryWindowed

ORDER BY

[IsGrandTotalRowTotal] DESC,

'User email (Export)'[User email],

'User email (Export)'[Domain]

The keep getting error message like: 
{
"error": {
"code": "DatasetExecuteQueriesError",
"pbi.error": {
"code": "DatasetExecuteQueriesError",
"parameters": {},
"details": [
{
"code": "DetailsMessage",
"detail": {
"type": 1,
"value": "Query (8, 40) Column '<oii>With activities</oii>' in table '<oii>User email (Export)</oii>' cannot be found or may not be used in this expression."
}
},
{
"code": "AnalysisServicesErrorCode",
"detail": {
"type": 1,
"value": "3241803780"
}
}
]
}
}
}

What is missing? Could anyone please help me?
Thanks,

[Anonymous]

Hi @Anonymous_10 
We wanted to follow up since we haven't heard back from you regarding our last response. We hope your issue has been resolved. If our answer resolved your query, please mark it as "Accept as Solution" and select "Yes" if it was helpful.If you need any further assistance, feel free to reach out.

Thank you for being a valued member of the Microsoft Fabric Community Forum!

[Anonymous_10]

Hi @Anonymous 
Sorry for delay in my response. I have been out of the town and I marked "accepted" for the solution provided by @lbendlin .
Thanks for the prompt response @lbendlin and @Anonymous thanks for the follow up.

[Anonymous]

Hi @Anonymous_10 ,
May I ask if you have gotten this issue resolved?
If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Thank you for being a valued member of the Microsoft Fabric Community Forum!

[Anonymous]

Hi @Anonymous_10 ,

I trust the @lbendlin response is accurate and will address your issue.

If you have any further questions or updates regarding your issue, feel free to ask, and we will look into that.

If the answer meets your requirements, please consider marking it as Accept as solution.

Thank you for being a valued member of the Microsoft Fabric Community Forum!

[lbendlin · Accepted solution]

Multiple issues here.  When running DAX queries you want to make them as simple and tabular as possible.  Use a table visual without totals.  Don't use a matrix visual as the basis.

Also, the ORDER BY clause is highly pointless and adds to the computational complexity.  Omit that whereever possible.

Use the DAX Query View or DAX Studio to simplify your DAX code before transferring the query to Power Automate or other external query tools.