WiseOwl Training - Established 1992 Wise Owl Training

Established May 1992
Wise Owl Training
See 525 reviews for our classroom and online training
If you found this blog useful and youâ€™d like to say thanks you can click here to make a contribution. Thanks for looking at our blogs!

BLOGS BY TOPIC

BLOGS BY AUTHOR

BLOGS BY YEAR

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.

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:

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.