Dax code not working in Power Automate

MrAtkinson — Tue, 23 Apr 2024 12:45:48 GMT

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

31m ago

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

Re: Dax code not working in Power Automate

Anonymous — Tue, 23 Apr 2024 14:14:01 GMT

Long shot, but trying removing the

'Calendar PQ'[Week number (month)],
1

from the TOPN part of the VAR __DS0PrimaryWindowed variable

topic Dax code not working in Power Automate in DAX Commands and Tips

Dax code not working in Power Automate

Re: Dax code not working in Power Automate