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
Thank you. I got it 🙂
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.
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
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.