BLOGS BY TOPIC▼
BLOGS BY AUTHOR▼
BLOGS BY YEAR▼
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.
- Power BI Desktop - March 2021 Update
- A new X-axis constant line
- 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.
- Power BI Desktop - March 2021 Update
- A new X-axis constant line
- Slight tweaks to DAX (this blog)