Performance Analyzer DAX Query run through Power Automate returns error: More than 1 result...

[JoelMTaylor]

Hello everyone,

I am running the Power Automate action "Run a query against a dataset" with the advice from the Blog (and fourmoo and Curbal). 

I copied the DAX Query from the Performance analyzer and ran it through Power Automate, but I received an error: "More than 1 result set has been encountered. Only 1 result set is supported." The DAX Query is just creating a table.

What do I need to change in the DAX Query for the flow to successfully output my table?

@avisingh , @ruthpozuelo  do you have any idea what I can fix?

My DAX Query:

DEFINE
  VAR __DS0FilterTable = 
    TREATAS({"A1"}, 'Students'[HC])

  VAR __DS0FilterTable2 = 
    FILTER(
      KEEPFILTERS(VALUES('Behaviour Issues'[Modified])),
      AND(
        'Behaviour Issues'[Modified] >= DATE(2022, 10, 23),
        'Behaviour Issues'[Modified] < DATE(2022, 10, 30)
      )
    )

  VAR __DS0Core = 
    SUMMARIZECOLUMNS(
      ROLLUPADDISSUBTOTAL(
        ROLLUPGROUP('Students'[Firstname], 'Students'[LASTNAME], 'Students'[Year], 'Students'[HC]), "IsGrandTotalRowTotal"
      ),
      __DS0FilterTable,
      __DS0FilterTable2,
      "v0_Grand_Total", 'Behaviour Issues'[0_Grand Total],
      "v1_Total_Behaviour", 'Behaviour Issues'[1_Total Behaviour],
      "v1a_Subtotal_Disruption", 'Behaviour Issues'[1a_Subtotal Disruption],
      "v1b_Subtotal_Inattentive", 'Behaviour Issues'[1b_Subtotal Inattentive],
      "v1c_Subtotal_Gum", 'Behaviour Issues'[1c_Subtotal Gum],
      "v2_Total_Organisation", 'Behaviour Issues'[2_Total Organisation],
      "v2a_Subtotal_Equipment", 'Behaviour Issues'[2a_Subtotal Equipment],
      "v2b_Subtotal_Homework", 'Behaviour Issues'[2b_Subtotal Homework],
      "v2c_Subtotal_Late", 'Behaviour Issues'[2c_Subtotal Late],
      "v3_Total_Uniform", 'Behaviour Issues'[3_Total Uniform],
      "v3a_Subtotal_Minor_Uniform_Issue", 'Behaviour Issues'[3a_Subtotal Minor Uniform Issue],
      "v3b_Subtotal_Shirt___Hair_Reminder", 'Behaviour Issues'[3b_Subtotal Shirt / Hair Reminder],
      "v3c_Subtotal_Hat", 'Behaviour Issues'[3c_Subtotal Hat],
      "v3d_Subtotal_Bag", 'Behaviour Issues'[3d_Subtotal Bag],
      "v3e_Subtotal_Jewellery", 'Behaviour Issues'[3e_Subtotal Jewellery]
    )

  VAR __DS0PrimaryShowAll = 
    ADDMISSINGITEMS(
      'Students'[Firstname],
      'Students'[LASTNAME],
      'Students'[Year],
      'Students'[HC],
      __DS0Core,
      ROLLUPISSUBTOTAL(
        ROLLUPGROUP('Students'[Firstname], 'Students'[LASTNAME], 'Students'[Year], 'Students'[HC]),
        [IsGrandTotalRowTotal]
      ),
      __DS0FilterTable,
      __DS0FilterTable2
    )

  VAR __DS0PrimaryWindowed = 
    TOPN(
      502,
      __DS0PrimaryShowAll,
      [IsGrandTotalRowTotal],
      0,
      [v0_Grand_Total],
      0,
      'Students'[Firstname],
      1,
      'Students'[LASTNAME],
      1,
      'Students'[Year],
      1,
      'Students'[HC],
      1
    )

  VAR __DS0PrimaryShowAllNoTotals = 
    FILTER(KEEPFILTERS(__DS0PrimaryShowAll), [IsGrandTotalRowTotal] = FALSE)

EVALUATE
  GROUPBY(
    __DS0PrimaryShowAllNoTotals,
    "Minv0_Grand_Total", MINX(CURRENTGROUP(), [v0_Grand_Total]),
    "Maxv0_Grand_Total", MAXX(CURRENTGROUP(), [v0_Grand_Total]),
    "Minv1_Total_Behaviour", MINX(CURRENTGROUP(), [v1_Total_Behaviour]),
    "Maxv1_Total_Behaviour", MAXX(CURRENTGROUP(), [v1_Total_Behaviour]),
    "Minv2_Total_Organisation", MINX(CURRENTGROUP(), [v2_Total_Organisation]),
    "Maxv2_Total_Organisation", MAXX(CURRENTGROUP(), [v2_Total_Organisation]),
    "Minv3_Total_Uniform", MINX(CURRENTGROUP(), [v3_Total_Uniform]),
    "Maxv3_Total_Uniform", MAXX(CURRENTGROUP(), [v3_Total_Uniform])
  )

EVALUATE
  __DS0PrimaryWindowed

ORDER BY
  [IsGrandTotalRowTotal] DESC,
  [v0_Grand_Total] DESC,
  'Students'[Firstname],
  'Students'[LASTNAME],
  'Students'[Year],
  'Students'[HC]

[johnt75 · Accepted solution]

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

[JoelMTaylor]

@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?

[johnt75]

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.

[JoelMTaylor]

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. 

[johnt75 · Accepted solution]

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

[JoelMTaylor]

Thank you, @johnt75.  

Is there a way to combine them - get the values with the grand total and everything else - into the first evaluate?

As a test, I tried to get rid of the TopN (as per @ruthpozuelo's video) and second evaluate (second evaluate just got the output of the TopN) and I received an error: 

"Query (70, 3) Column 'IsGrandTotalRowTotal' cannot be found or may not be used in this expression."

[johnt75 · Accepted solution]

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]

[JoelMTaylor]

I tried that, but the result wasn't correct - I am missing most columns.

[I deleted the evaluate sections as described.]

[Output]

[johnt75]

Try running it in DAX Studio or Tabular Editor and see what that outputs