Answered

Power Automate and Power BI DAX - how to formate?

(0) Share

Report

Posted on by 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],

'Dates'[Date],

'Items'[No],

'Items'[Description],

)

EVALUATE

__DS0PrimaryWindowed

ORDER BY

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

BR

Matt

Categories:

General topics

I have the same question (0)

All responses (4)

Answers (1)

lbendlin 8,535 Super User 2025 Season 2 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Matt78 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Verified answer

lbendlin 8,535 Super User 2025 Season 2 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Matt78 on at

Like (0)

Report
Copy link

Link copied!

Thank you. I got it 🙂

Was this reply helpful? Yes No