WiseOwl Training - Established 1992 Wise Owl Training

Established May 1992
Wise Owl Training
See 516 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

Power BI Desktop - March 2021 Update
Part three of a three-part series of blogs

There are two tiny changes in the March 2021 update - X-axis constant lines and a couple of tweaks to DAX - but still the big logjam of changes remains in preview.

1. Power BI Desktop - March 2021 Update
2. A new X-axis constant line
3. Slight tweaks to DAX (this blog)

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 24 March 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.

# Slight tweaks to DAX

These barely merit their own blog page, but ...

## The IF.EAGER function

The mark of a niche function is when you have to rack your brains to think of an example of when you'd use it, but here goes!

Suppose you're showing the total quantity of sales for the current and previous month for a model:

I've created two measures called This month and Last month to do this.

You now want to show the higher of the two for each month (and have inexplicably forgotten that you could use the MAX function to do this), so you create this third measure:

Bigger each month = IF.EAGER(

// the IF.EAGER function will evaluate the

// values for this month and last month

// once only

[Last month] > [This month],

[Last month],

[This month]

)

Here's what this would show for the first few months:

Your new measure picks out whichever of the other two measures is bigger for the current time period.

The difference between IF.EAGER and plain old IF is this - for IF.EAGER, Power BI will evaluate each of the three parts of the function regardless of whether the condition being tested is true.  So if the condition is true, for example, here's how many times each measure would be evaluated:

Function This month Last month
IF Once Twice (once for the condition, once for the second argument)
IF.EAGER Once Once only

So eager evaluation like this can save time, although you do get the feeling that the DAX team were scraping the bottom of the barrel with this one.  This is particularly true because as far as I can see you could avoid the IF.EAGER function by using variables - for example:

Bigger and better =

// get the values of each measures

VAR ThisMonth = [This month]

VAR LastMonth = [Last month]

// return the higher

RETURN IF(

LastMonth > ThisMonth,

LastMonth,

ThisMonth

)

## Multiple filters for the CALCULATE function

Consider this measure:

Stockport or small = CALCULATE(

// calculate total qty sold ...

SUM(Purchase[Quantity]),

// ... for small centres or ones

// in the SK postcode

Centre[NumberUnits] < 10 ||

LEFT(Centre[PostCode],2) = "SK"

)

It calculates the total quantity of purchases for shopping centres which either have less than 10 units or are in Stockport.  So what's special about this?  Well, you couldn't actually do it like this previously - instead you'd have had to create a more complicated measure including a FILTER function.

However, this only works if the two columns are in the same table.  Suppose you try creating a measure to show the total quantity sold where the number of units is more than 10 or the product in question has 4 legs:

Medium sales = CALCULATE(

// calculate total qty sold ...

SUM(Purchase[Quantity]),

// ... for small centres or quadrupeds

Centre[NumberUnits] > 10 ||

'Product'[Legs] = 4

)

This would still give this familiar error message:

You still can't combine columns from multiple tables in a filter.

Note that all of the above is only an issue when you're using OR.  If you're using AND (you want all of the conditions to be true) you can just add each condition as a separate argument.