Power Automate and Power BI DAX - how to formate?

[Matt78]

Hi all,

I have DAX query created by Power BI, and I have flow which creates html tabel to my email. This works fine but I would like to format the results a bit. How can I format date and headers? Now dates are in format 2024-04-29T05:44:08 and headers "IsGrandTotalRowTotal". I would like date to be dd/mm/yyyy and header for example "Grand Total"?

--DEFINE

VAR __DS0FilterTable =

FILTER(

KEEPFILTERS(VALUES('Dates'[Date])),

'Dates'[Date] >= (DATE(2022, 12, 31) + TIME(0, 0, 1))

)

VAR __DS0FilterTable2 =

TREATAS({3}, 'Item Ledger Entry '[Entry Type])

VAR __DS0FilterTable3 =

TREATAS({"Today"}, 'Dates'[Slicer Date])

VAR __DS0FilterTable4 =

TREATAS({"PPP"}, 'Purch Rec Lines'[Location Code])

VAR __DS0Core =

SUMMARIZECOLUMNS(

ROLLUPADDISSUBTOTAL(

ROLLUPGROUP('Dates'[Date], 'Items'[No], 'Items'[Description]), "IsGrandTotalRowTotal"

),

__DS0FilterTable,

__DS0FilterTable2,

__DS0FilterTable3,

__DS0FilterTable4,

"SumQuantity", CALCULATE(SUM('Purch Rec Lines'[Quantity])),

"Item_Ledger_Entry_Quantity", CALCULATE(SUM('Item Ledger Entry '[Quantity])),

"Quantity_plus_Quantity2", 'Dates'[Quantity plus Quantity2],

"New_Item", 'Dates'[New Item]

)

VAR __DS0PrimaryWindowed =

TOPN(

502,

__DS0Core,

[IsGrandTotalRowTotal],

0,

'Dates'[Date],

1,

'Items'[No],

1,

'Items'[Description],

1

)

EVALUATE

__DS0PrimaryWindowed

ORDER BY

[IsGrandTotalRowTotal] DESC, 'Dates'[Date], 'Items'[No], 'Items'[Description]--


BR

Matt

[lbendlin]

When you borrow DAX code from a Power BI visual you want to first change the visual to Table and then disable the totals. That will result in much simpler DAX.

Then you can use a "Select" action on "First Table Rows"  to remap the results.

[Matt78]

Hello

Thank you for the quick reply. Which actions I need here and in which order?

Run a query against a dataset

Select

Create HTML table

When I use Select how can I remap the results, it requires key and value?

br

Matt

[lbendlin · Accepted solution]

That is the right order of the steps. Key is your desired column name, Value is the content of the original column as it comes from the DAX query.  The Select action automatically enumerates the incoming array.

Try without the Select first,  create the HTML table directly from the First Table Rows.  Then you will see the difference.

[Matt78]

Thank you. I got it 🙂