Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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]
)