Forum Discussion
Performance Analyzer DAX Query run through Power Automate returns error: More than 1 result...
There are 2 EVALUATE statements, the first one is returning the values without the grand total and the second one is returning everything including the grand total. Decide which one you want in your power flow and delete the other EVALUATE statement
Delete everything from the first EVALUATE to the end and replace it with
EVALUATE __DS0PrimaryShowAll ORDER BY [IsGrandTotalRowTotal] DESC, [v0_Grand_Total] DESC, 'Students'[Firstname], 'Students'[LASTNAME], 'Students'[Year], 'Students'[HC]
johnt75, the query worked in DAX Studio, and I have since got it to work in Power Automate. However, there is one problem. The CSV has the columns in the wrong order. I am trying to figure out a way to arrange them properly.
Do you know of a method to arrange them properly? I thought about mapping the columns individually with Select. However, I couldn't figure out how to reference the headers.
The JSON output from PowerBI looks like below. However, if I reference "outputs('Run_a_query_for_HC')?['body/firstTableRows'][0]", it gets the whole object?
I think you will probably need to implement a loop which goes through the results row by row and then builds a string to output the fields in the order that you need.
You're right. I managed to do exactly that. I used an apply to each to build an array every new line. The first row had the headers in the wrong order, so I skipped that and used a 'do until' to repeat the second time and a counter and condition to only do the rows 2-26 and 27 (first repeated).
However, I realised later that the Query was sorting by IsGrandTotalRow - which I could change to ASC to put my troublesome first row to the end - thus eliminating the need for the 'do until'.
Below is an overview of my flow.
Thank you for your assistance, johnt75.