Answered

run a query against a dataset empty body

(0) Share

Report

Posted on by jgabrielson

I'm banging my head against the wall with this issue hoping someone can point out a simple step that I've missed. Essentially I've been trying to use run a query against a powerbi dataset in power automate to get data from a powerbi report in to power automate. The instructions I'm finding all seem simple enough and I've followed them to the letter but my query is returning no results in the body, regardless of the report.

The steps I've taken here:

- connected to powerbi inside of the powerautomate

- copied the DAX query from PBI Desktop performance analyzer in to the query box of the power automate step

- tried to create a simple html table from the results.

When I run, I see that the body results are empty despite my powerbi dataset and DAX table containing data.

Any help would be huge! Thank you!!

2023-12-25 - 08-18-03 - Microsoft Power Automate.png

My DAX query

// DAX Query
DEFINE
 VAR __DS0Core = 
 SUMMARIZECOLUMNS(
 ROLLUPADDISSUBTOTAL('Table'[Column2], "IsGrandTotalRowTotal"),
 "SumColumn1", CALCULATE(SUM('Table'[Column1]))
 )

 VAR __DS0PrimaryWindowed = 
 TOPN(502, __DS0Core, [IsGrandTotalRowTotal], 0, 'Table'[Column2], 1)

EVALUATE
 __DS0PrimaryWindowed

ORDER BY
 [IsGrandTotalRowTotal] DESC, 'Table'[Column2]

Categories:

General topics

I have the same question (0)

All responses (4)

Answers (1)

lbendlin 8,479 Super User 2025 Season 2 on at

Like (0)

Report
That query has an unnecessary totals row. Try this query instead:

EVALUATE SUMMARIZECOLUMNS( 'Table'[Column2], "SumColumn1", CALCULATE(SUM('Table'[Column1])) )

Was this reply helpful? Yes No
jgabrielson 29 on at

Like (0)

Report

That worked! Thank you. It seems that the DAX interpreter in powerautomate is too basic for advanced DAX queries. I've tested some this morning and you basically need to boil down the DAX query to a basic EVALUATE statement. remove the VAR declarations, filters, and orderby and that seems to work. Does that sound right? It would be nice if we could at least incorporate filters but I'm not 100% certain that we can't.. Does that sound right?

Thank you very much for your help!!

Was this reply helpful? Yes No
jgabrielson 29 on at

Like (0)

Report

scratch that.. it seems just the VARs need to be removed. Everything needs to be incorporated into one EVALUATE statement... or at least that's what I'm seeing.

Was this reply helpful? Yes No
Verified answer

lbendlin 8,479 Super User 2025 Season 2 on at

Like (0)

Report

I don't think Power Automate knows anything about DAX. It merely hands over the query text to the ~~dataset~~ semantic model and wants a table back. You can structure the DAX any way you want - which means stripping out unnecessary totals , mostly.

Remember that ominous "First Table Rows". Means that while you can have multiple EVALUATE in your DAX, only the first one will actually be used.

Was this reply helpful? Yes No