Dax code from power bi performance analyzer doesn't work when put into power automate flow.

[MrAtkinson]

Hi all,

I am trying to have power automate export a particular matrix table and and save it into sharepoint. I have created the flow but the code i am pasting for the matrix i need exporting doesnt work. Im not sure how as i used performance analyser and recorded the tables being refreshed to get the code. 

Any help would be great

I am trying to replicate this in an exported excel document with the DAX code below

// DAX Query
DEFINE
  VAR __DS0FilterTable = 
    TREATAS(
      {"Armadillo",
        "Director",
        "Essentials",
        "Kickstart",
        "Plus",
        "Standard",
        "Umbrella"},
      'si_membership'[Grade]
    )

  VAR __DS0FilterTable2 = 
    TREATAS({"Valid",
      "Pending Cancelled"}, 'si_membership'[Subscription Status])

  VAR __DS0Core = 
    SUMMARIZECOLUMNS(
      ROLLUPADDISSUBTOTAL('Calendar PQ'[Week number (month)], "IsGrandTotalRowTotal"),
      ROLLUPADDISSUBTOTAL('si_membership'[Grade], "IsGrandTotalColumnTotal"),
      __DS0FilterTable,
      __DS0FilterTable2,
      "Count_of_Subscription", 'Tenure calculations'[Count of Subscription]
    )

  VAR __DS0PrimaryWindowed = 
    TOPN(
      102,
      SUMMARIZE(__DS0Core, 'Calendar PQ'[Week number (month)], [IsGrandTotalRowTotal]),
      [IsGrandTotalRowTotal],
      0,
      'Calendar PQ'[Week number (month)],
      1
    )

  VAR __DS0SecondaryBase = 
    SUMMARIZE(__DS0Core, 'si_membership'[Grade], [IsGrandTotalColumnTotal])

  VAR __DS0Secondary = 
    TOPN(102, __DS0SecondaryBase, [IsGrandTotalColumnTotal], 1, 'si_membership'[Grade], 1)

  VAR __DS0BodyLimited = 
    NATURALLEFTOUTERJOIN(
      __DS0PrimaryWindowed,
      SUBSTITUTEWITHINDEX(
        __DS0Core,
        "ColumnIndex",
        __DS0Secondary,
        [IsGrandTotalColumnTotal],
        ASC,
        'si_membership'[Grade],
        ASC
      )
    )

EVALUATE
  __DS0Secondary

ORDER BY
  [IsGrandTotalColumnTotal], 'si_membership'[Grade]

EVALUATE
  __DS0BodyLimited

ORDER BY
  [IsGrandTotalRowTotal] DESC, 'Calendar PQ'[Week number (month)], [ColumnIndex]

has context menu

[lbendlin]

When you borrow DAX code from Power BI visuals you don't want to have totals. Disable them and you will see the code becomes much simpler.

// DAX Query
EVALUATE
  VAR __DS0FilterTable = 
    TREATAS(
      {"Armadillo",
        "Director",
        "Essentials",
        "Kickstart",
        "Plus",
        "Standard",
        "Umbrella"},
      'si_membership'[Grade]
    )

 

  VAR __DS0FilterTable2 = 
    TREATAS({"Valid",
      "Pending Cancelled"}, 'si_membership'[Subscription Status])

 

RETURN SUMMARIZECOLUMNS(
      'Calendar PQ'[Week number (month)],
      'si_membership'[Grade],
      __DS0FilterTable,
      __DS0FilterTable2,
      "Count_of_Subscription", [Count of Subscription]
    )