Browse 536 attributed reviews, viewable separately for our classroom and online training
September 2021 Power BI Update
Part six of a seven-part series of blogs

The September 2021 update to Power BI introduces even more features for customising buttons, a Power Automate connector and line chart series labels, along with a few other small changes.

  1. September 2021 Power BI Update
  2. You can use shapes and icons as buttons
  3. Running Power Automate flows from Power BI reports
  4. You can now label line chart series
  5. Suppressing totals on waterfall charts
  6. Changes to DAX (this blog)
  7. Pictorial histograms using the Graphomate custom visual

We've been creating our idiosyncratic monthly blogs on Power BI updates since November 2016, and also deliver online and classroom Power BI courses.

Posted by Andy Brown on 28 September 2021

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Changes to DAX

This month sees two small changes to DAX: a simplified way to write the CALCULATE and CALCULATETABLE functions, and a new argument when formatting dates.

Calculating using aggregate functions

If you're doing a simple logical test, you can include aggregate functions within a CALCULATE or CALCULATETABLE function.  For example:

Extreme sales = CALCULATE(

// calculate total sales ...

SUMX(

Purchase,

[Price] * [Quantity]

),

// ... where the price is lower

// than average and the quantity higher

Purchase[Price] < AVERAGE(Purchase[Price]) &&

Purchase[Quantity] > AVERAGE(Purchase[Quantity])

)

I have two problems with this.  The first is that (confession, here) I didn't realise that you couldn't already do this.  The second is that this formula could be better written using variables:

Extreme sales =

// calculate average price and quantity

VAR AveragePrice = AVERAGE(Purchase[Price])

VAR AverageQty = AVERAGE(Purchase[Quantity])

RETURN

CALCULATE(

// calculate total sales ...

SUMX(

Purchase,

[Price] * [Quantity]

),

// ... where the price is lower

// than average and the quantity higher

Purchase[Price] < AveragePrice &&

Purchase[Quantity] > AverageQty

)

Formatting dates to specific locales

When formatting dates, there's now a third argument to the FORMAT function:

Formatting dates

You can now give the name of where you are.

For example, if you want to show dates in the UK style, you could use a formula like this:

Formatted date = FORMAT(

// format the purchase date

[PurchaseDate],

// using no particular format

BLANK(),

// using the UK style

"en-GB"

)

However, there is again an easier way to achieve this using existing DAX syntax:

Formatted date = FORMAT(

// format the purchase date

[PurchaseDate],

// using a specific format

"dd/MM/yyyy"

)

The above two formulae would (in the UK anyway) give exactly the same result - for example 9/11 would render as 11/09/2001. 

This blog has 0 threads Add post