Skip to main content

Notifications

Power Automate - General Discussion
Answered

Power Automate and Power BI DAX - how to formate?

(0) ShareShare
ReportReport
Posted on by

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

Categories:
  • Matt78 Profile Picture
    Matt78 on at
    Re: Power Automate and Power BI DAX - how to formate?

    Thank you. I got it 🙂

  • Verified answer
    lbendlin Profile Picture
    lbendlin 7,597 on at
    Re: Power Automate and Power BI DAX - how to formate?

    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 Profile Picture
    Matt78 on at
    Re: Power Automate and Power BI DAX - how to formate?

    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 Profile Picture
    lbendlin 7,597 on at
    Re: Power Automate and Power BI DAX - how to formate?

    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.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,495

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,822

Leaderboard