Blog Post

Power BI Community Blog
1 MIN READ

How did I format my DAX expression in a post?

DataZoe's avatar
DataZoe
Icon for Microsoft Employee rankMicrosoft Employee
5 years ago

To format a DAX expression, I utilize the HTML view of the posts to copy in a formatted div tag and the html of the formatted DAX expression generated by https://www.daxformatter.com/.

 

  1. Click on the HTML button in the top bar of the post (you may need to click "..." to have it show)
  2. Paste in the <div></div> tag below.
  3. Copy DAX expression.
  4. Paste into https://www.daxformatter.com/ and then click "Copy HTML".
  5. Paste in the HTML DAX code from dax formatter before </div> (where the comment is).
  6. Click OK.

The formatted <div> tag (which can be further modified as you like!):

 

<div style="background: #F5F2F0; border: 1px solid lightgrey; border-radius: 10px; padding: 10px 10px 10px 10px; font-family: Courier, monospace; margin: 5px 5px 5px 5px; font-size: 12px;">
<!-- Insert DAXFormatter.com HTML code here -->
</div>

 

 

This will format the DAX expression to look like this:

Measure Cumulative =
CALCULATE (
    [Measure],
    FILTER (
        ALLSELECTED ( 'Date'[Date] ),
        ISONORAFTER ( 'Date'[Date], MAX ( 'Date'[Date] ), DESC )
    )
)
Updated 5 years ago
Version 3.0
how to
tips & tricks
DataZoe's avatar
Icon for Microsoft Employee rankMicrosoft Employee
Joined April 03, 2020
View Profile
Power BI Community Blog
On this blog you will find articles, guides and information created by our community members and, from time to time, roundups and community news. If you are interested in becoming a community blog author, send a private message to Natalie_iTalent. An overview of the community blogs can be found here.

5 Comments

  • DataZoe's avatar
    DataZoe
    Icon for Microsoft Employee rankMicrosoft Employee
    5 years ago

    daxer-almighty You are right! I forgot to mention you need to CLOSE the div tag after pasting in the HTML DAX with the </div>

     

    I will paste in the whole <div .... > </div> then paste the formatted DAX between the tags:

     

     

     

    <div style="background: #F5F2F0; border: 1px solid lightgrey; border-radius: 10px; padding: 10px 10px 10px 10px; font-family: Courier, monospace; margin: 5px 5px 5px 5px; font-size: 12px;">
<!-- insert DAX formatter HTML here -->
</div>

     

     

     

    [7D Rolling Avg] =
    VAR __daysCountRequired = 7
    VAR __lastVisibleDay =
        MAX ( 'Event Dates'[Date] ) // The assumption here is that if there is
    // an entry in the FactTable for a particular
    // day, it means the measure you're trying to
    // average is not blank for this day.
    VAR __periodToAverageOver =
        TOPN (
            __daysCountRequired,
            SUMMARIZE (
                CALCULATETABLE ( FactTable, 'Event Dates'[Date] <= __lastVisibleDay ),
                'Event Dates'[Date]
            ),
            'Event Dates'[Date], DESC
        )
    VAR __shouldCalculate =
        COUNTROWS ( __periodToAverageOver ) = __daysCountRequired
    VAR __average =
        IF ( __shouldCalculate, AVERAGEX ( __periodToAverageOver, [Your Measure] ) )
    RETURN
        __average

     

    Thank you for bringing this to my attention and I'll work to get the post updated!

  • daxer-almighty's avatar
    daxer-almighty
    Icon for Solution Sage rankSolution Sage
    5 years ago
    Does not work. I get an error that says: "Your post has been changed because invalid HTML was found in the message body. The invalid HTML has been removed. Please review the message and submit the message when you are satisfied." This is despite me following your recipe to the letter 😞
     
    [7D Rolling Avg] =
    VAR __daysCountRequired = 7
    VAR __lastVisibleDay =
        MAX ( 'Event Dates'[Date] ) // The assumption here is that if there is
    // an entry in the FactTable for a particular
    // day, it means the measure you're trying to
    // average is not blank for this day.
    VAR __periodToAverageOver =
        TOPN (
            __daysCountRequired,
            SUMMARIZE (
                CALCULATETABLE ( FactTable, 'Event Dates'[Date] <= __lastVisibleDay ),
                'Event Dates'[Date]
            ),
            'Event Dates'[Date], DESC
        )
    VAR __shouldCalculate =
        COUNTROWS ( __periodToAverageOver ) = __daysCountRequired
    VAR __average =
        IF ( __shouldCalculate, AVERAGEX ( __periodToAverageOver, [Your Measure] ) )
    RETURN
        __average
  • MSpedaletti's avatar
    MSpedaletti
    Regular Visitor
    2 years ago

     

    <div style="background: #F5F2F0; border: 1px solid lightgrey; border-radius: 10px; padding: 10px 10px 10px 10px; font-family: Courier, monospace; margin: 5px 5px 5px 5px; font-size: 12px;">
Família&nbsp;=<br><span class="Keyword" style="color:#035aca">SUMMARIZECOLUMNS</span><span class="Parenthesis" style="color:#808080">&nbsp;(</span><br><span class="indent4">&nbsp;&nbsp;&nbsp;&nbsp;</span>'12MPP'[Data&nbsp;Rodada],<br><span class="indent4">&nbsp;&nbsp;&nbsp;&nbsp;</span>'12MPP'[Data&nbsp;Rodada_YM],<br><span class="indent4">&nbsp;&nbsp;&nbsp;&nbsp;</span>'12MPP'[Data&nbsp;SAT_YM],<br><span class="indent4">&nbsp;&nbsp;&nbsp;&nbsp;</span>'Dim_Veículo'[Tipo],<br><span class="indent4">&nbsp;&nbsp;&nbsp;&nbsp;</span>'Dim_Veículo'[Família],<br><span class="indent4">&nbsp;&nbsp;&nbsp;&nbsp;</span><span class="StringLiteral" style="color:#D93124">"Quantidade"</span>,&nbsp;<span class="Keyword" style="color:#035aca">SUM</span><span class="Parenthesis" style="color:#808080">&nbsp;(</span>&nbsp;'12MPP'[Quantidade]&nbsp;<span class="Parenthesis" style="color:#808080">)</span>,<br><span class="indent4">&nbsp;&nbsp;&nbsp;&nbsp;</span><span class="StringLiteral" style="color:#D93124">"Gabarito"</span>,&nbsp;<span class="Keyword" style="color:#035aca">SUM</span><span class="Parenthesis" style="color:#808080">&nbsp;(</span>&nbsp;Dim_Gabarito[Quantidade]&nbsp;<span class="Parenthesis" style="color:#808080">)</span>,<br><span class="indent4">&nbsp;&nbsp;&nbsp;&nbsp;</span><span class="StringLiteral" style="color:#D93124">"Ranking"</span>,&nbsp;<span class="Keyword" style="color:#035aca">AVERAGE</span><span class="Parenthesis" style="color:#808080">&nbsp;(</span>&nbsp;Dim_Ranking[Ranking]&nbsp;<span class="Parenthesis" style="color:#808080">)</span>,<br><span class="indent4">&nbsp;&nbsp;&nbsp;&nbsp;</span><span class="StringLiteral" style="color:#D93124">"Peso"</span>,&nbsp;<span class="Keyword" style="color:#035aca">AVERAGE</span><span class="Parenthesis" style="color:#808080">&nbsp;(</span>&nbsp;Dim_Ranking[Peso]&nbsp;<span class="Parenthesis" style="color:#808080">)</span>,<br><span class="indent4">&nbsp;&nbsp;&nbsp;&nbsp;</span><span class="StringLiteral" style="color:#D93124">"Erro&nbsp;ABS"</span>,<br><span class="indent8">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span><span class="Keyword" style="color:#035aca">ABS</span><span class="Parenthesis" style="color:#808080">&nbsp;(</span><br><span class="indent8">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span><span class="indent4">&nbsp;&nbsp;&nbsp;&nbsp;</span><span class="Keyword" style="color:#035aca">SUMX</span><span class="Parenthesis" style="color:#808080">&nbsp;(</span>&nbsp;Dim_Gabarito,&nbsp;Dim_Gabarito[Quantidade]&nbsp;<span class="Parenthesis" style="color:#808080">)</span><br><span class="indent8">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span><span class="indent8">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span>-&nbsp;<span class="Keyword" style="color:#035aca">SUMX</span><span class="Parenthesis" style="color:#808080">&nbsp;(</span>&nbsp;'12MPP',&nbsp;'12MPP'[Quantidade]&nbsp;<span class="Parenthesis" style="color:#808080">)</span><br><span class="indent8">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span><span class="Parenthesis" style="color:#808080">)</span><br><span class="Parenthesis" style="color:#808080">)</span><br>
</div>

     

    Tried to test it and it does not seem to work