bqdirectquery

// DAX Query
DEFINE
  VAR __DS0FilterTable = 
    TREATAS({"Coal",
      "Renewable",
      "Fuel"}, 'UNITARCHIVE'[Technology])

  VAR __DS0FilterTable2 = 
    FILTER(
      KEEPFILTERS(VALUES('UNITARCHIVE'[SETTLEMENTDATE])),
      AND(
        'UNITARCHIVE'[SETTLEMENTDATE] >= DATE(2019, 3, 26),
        'UNITARCHIVE'[SETTLEMENTDATE] < DATE(2020, 3, 25)
      )
    )

  VAR __DS0Core = 
    SUMMARIZECOLUMNS(
      ROLLUPADDISSUBTOTAL(
        'UNITARCHIVE'[Region], "IsGrandTotalRowTotal",
        'UNITARCHIVE'[StationName], "IsDM1Total"
      ),
      ROLLUPADDISSUBTOTAL('UNITARCHIVE'[Technology], "IsGrandTotalColumnTotal"),
      __DS0FilterTable,
      __DS0FilterTable2,
      "SumINITIALMW", CALCULATE(SUM('UNITARCHIVE'[INITIALMW]))
    )

  VAR __DS0PrimaryWindowed = 
    TOPN(
      102,
      SUMMARIZE(
        __DS0Core,
        'UNITARCHIVE'[Region],
        [IsGrandTotalRowTotal],
        'UNITARCHIVE'[StationName],
        [IsDM1Total]
      ),
      [IsGrandTotalRowTotal],
      0,
      'UNITARCHIVE'[Region],
      1,
      [IsDM1Total],
      0,
      'UNITARCHIVE'[StationName],
      1
    )

  VAR __DS0SecondaryBase = 
    SUMMARIZE(__DS0Core, 'UNITARCHIVE'[Technology], [IsGrandTotalColumnTotal])

  VAR __DS0Secondary = 
    TOPN(102, __DS0SecondaryBase, [IsGrandTotalColumnTotal], 1, 'UNITARCHIVE'[Technology], 1)

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

EVALUATE
  __DS0Secondary

ORDER BY
  [IsGrandTotalColumnTotal], 'UNITARCHIVE'[Technology]

EVALUATE
  __DS0BodyLimited

ORDER BY
  [IsGrandTotalRowTotal] DESC,
  'UNITARCHIVE'[Region],
  [IsDM1Total] DESC,
  'UNITARCHIVE'[StationName],
  [ColumnIndex]