Today I feel like a newbie. It’s the first time I had a need to use the DAX RelatedTable() function…

Advertisements

In my post from yesterday, I showed a kind of messy DAX calculation from a workbook I’m building. I spent some time today trying to simplify the calculation. Here’s V2 of the calculation which is (I think) a lot less messy and hard to understand:

=IF([Date] <= [DateToday] – WEEKDAY([DateToday]) – 1,
SWITCH(TRUE(),
[Date] >= [DateToday] – WEEKDAY([DateToday]) – 7, 0,
[Date] >= [DateToday] – WEEKDAY([DateToday]) – (2*7), 1,
[Date] >= [DateToday] – WEEKDAY([DateToday]) – (3*7), 2,
[Date] >= [DateToday] – WEEKDAY([DateToday]) – (4*7), 3,
[Date] >= [DateToday] – WEEKDAY([DateToday]) – (5*7), 4,
[Date] >= [DateToday] – WEEKDAY([DateToday]) – (6*7), 5,
[Date] >= [DateToday] – WEEKDAY([DateToday]) – (7*7), 6,
[Date] >= [DateToday] – WEEKDAY([DateToday]) – (8*7), 7,
[Date] >= [DateToday] – WEEKDAY([DateToday]) – (9*7), 8,
[Date] >= [DateToday] – WEEKDAY([DateToday]) – (10*7), 9,
[Date] >= [DateToday] – WEEKDAY([DateToday]) – (11*7), 10,
[Date] >= [DateToday] – WEEKDAY([DateToday]) – (12*7), 11,
BLANK()),
BLANK())

[Date] (formerly [DateValue]) is the column containing the date value of the row with the time part stripped off.

In the calculation itself, I first check if [Date] is later than the previous full Saturday – Friday week and if so, return BLANK() (the IF() function). If not, I use the SWITCH() function to peel off the weeks back one at a time until I get to the twelfth week. If the row is older than that, I return BLANK().

In researching the SWITCH() function, I found that, from an execution standpoint, the results are guaranteed to be evaluated in the order they appear in the SWITCH(). Because of that, I don’t have to check both ends of each weekly range. This de-clutters the calculation a lot.

Note, however, that while the result of the calculation is guaranteed from an order point of view, the actual execution of the SWITCH() cases is not guaranteed to be in order and short circuit evaluation is not guaranteed. So you can’t rely on cases not being executed if they come after the case that ends up being the one that fires and you can’t rely on the cases being executed in the order they appear.

OK, here is the DAX calculation I’m not particularly proud of:

=IF([DateValue] >= ([NowValue] – WEEKDAY([NowValue]) – (0 * 7) – 7) &&
[DateValue] <= ([NowValue] – WEEKDAY([NowValue]) – (0 * 7) – 1), 0,
IF([DateValue] >= ([NowValue] – WEEKDAY([NowValue]) – (1 * 7) – 7) &&
[DateValue] <= ([NowValue] – WEEKDAY([NowValue]) – (1 * 7) – 1), 1,
IF([DateValue] >= ([NowValue] – WEEKDAY([NowValue]) – (2 * 7) – 7) &&
[DateValue] <= ([NowValue] – WEEKDAY([NowValue]) – (2 * 7) – 1), 2,
IF([DateValue] >= ([NowValue] – WEEKDAY([NowValue]) – (3 * 7) – 7) &&
[DateValue] <= ([NowValue] – WEEKDAY([NowValue]) – (3 * 7) – 1), 3,
IF([DateValue] >= ([NowValue] – WEEKDAY([NowValue]) – (4 * 7) – 7) &&
[DateValue] <= ([NowValue] – WEEKDAY([NowValue]) – (4 * 7) – 1), 4,
IF([DateValue] >= ([NowValue] – WEEKDAY([NowValue]) – (5 * 7) – 7) &&
[DateValue] <= ([NowValue] – WEEKDAY([NowValue]) – (5 * 7) – 1), 5,
IF([DateValue] >= ([NowValue] – WEEKDAY([NowValue]) – (6 * 7) – 7) &&
[DateValue] <= ([NowValue] – WEEKDAY([NowValue]) – (6 * 7) – 1), 6,
IF([DateValue] >= ([NowValue] – WEEKDAY([NowValue]) – (7 * 7) – 7) &&
[DateValue] <= ([NowValue] – WEEKDAY([NowValue]) – (7 * 7) – 1), 7,
IF([DateValue] >= ([NowValue] – WEEKDAY([NowValue]) – (8 * 7) – 7) &&
[DateValue] <= ([NowValue] – WEEKDAY([NowValue]) – (8 * 7) – 1), 8,
IF([DateValue] >= ([NowValue] – WEEKDAY([NowValue]) – (9 * 7) – 7) &&
[DateValue] <= ([NowValue] – WEEKDAY([NowValue]) – (9 * 7) – 1), 9,
IF([DateValue] >= ([NowValue] – WEEKDAY([NowValue]) – (10 * 7) – 7) &&
[DateValue] <= ([NowValue] – WEEKDAY([NowValue]) – (10 * 7) – 1), 10,
IF([DateValue] >= ([NowValue] – WEEKDAY([NowValue]) – (11 * 7) – 7) &&
[DateValue] <= ([NowValue] – WEEKDAY([NowValue]) – (11 * 7) – 1), 11,
BLANK()))))))))))))

What does it do?

It is a calculated column that calculates the number of weeks back a given row’s date field (the [DateValue] column) is from the current week. The catch is that for this calculation the week starts on Saturday. [DateValue] is a calculated column that strips out the time part of a datetime value and [NowValue] does the same for the NOW() function. I only want to calculate 12 weeks back. I also want to calculation to give the right answer every time the calculation is updated.

Anyone have a better way to do this?

Spent some time this evening writing some very convoluted DAX to do something that I think should be more simple. Basically, I wanted to bucket-ize dates from fact table records into specific weeks. The catch is that the weeks don’t fall on normal calendar boundaries. I’ll post what I came up with tomorrow but other solutions are welcomed.

Power Query is one of the newer parts of the Power BI offering. I haven’t been able to find any books (yet) about it so the web is the best bet for learning about it now. I added a sub-page of my Microsoft BI Resrouces page specifically for Power Query. I’ll be adding links to what I find related to Power Query. Apart from the documentation from Microsoft, I found that Chris Webb’s blog has a number of posts related to Power Query.

It’s been a good year so far. I’ve been successful in starting to build the habit of exercising 6 days a week per my doctor’s instructions. I’ve also been getting enough sleep (reading about the effects of not enough sleep has been motivation there). Things at work are in the process of changing for the better. All in all I feel like good momentum is building.