Russo's formula doesn't work

[nemo]

This is the formula in page 139 of the book The Definitive Guide to DAX (first edition) by Marco Russo and Alberto Ferrari

Where 

However, it returned BLANK when tested with the data!!!

Could someone please explain what error(s) had the authors made in that formula?

[ValtteriN]

Hi,

The formula seems to be correct. It filters Sales table to only include numbers from 2007 and then calculates sales over the filtered table so that Sales[quantity] is multiplied by Sales[unit Price]. Since the formlua is returning blank check the following things:

1. Is there sales data for the year 2007
2. Is your [DeliveryDateKey] and [DateKey] formatted in the same way -> e.g. both should be Date or both should be Date/Time
3. Double check your relationships


You can easily check if the measure is working as expected by taking the FILTER portion of the dax and creating a calculated table using "New table" or placing the dax inside EVALUATE in the new section of Power BI:

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[nemo]

Hi Valtteri,

Thank you for your response.

Followed your advice, I found that the authors made a typo: it should be 'Date'[Date] instead of 'Date'[DateKey].

Although the test table (evaluated from the FILTER(CALCULATETABLE...) did return values, the measure Delivered Amount in 2007 was still blank. 

Was this a bug with Power BI?

[ValtteriN]

Hi,

Was there also data in your Test[Unit Price]? All in all, based on this test I would say the measure is correct and i suspect some other issue with your filter context is resulting in this blank value. 

[nemo]

Hi Valtteri,

Thank you for your response.

The Test Table did return values for all columns for 2007 only. I was able to use that Test Table to successfully create the Test Measure (sumx('Test Table', 'Test Table'[Unit Price] * 'Test Table'[Quantity]).

As shown in the screenshot below, there were no filters applied, but the formula in question still showed BLANK value.

What other possible issues that caused this problem?

[ValtteriN]

Hi,

This is quite odd. If the test table is truly the same the dax you have in your FILTER condition there shouldn't be any further issues. 

Generally speaking in these kind of situations I usually start by double cheking the data types for any missmatches. Check that all the columns participating in the formula have same data types. So e.g. Date in the case of 'Calendar'[Date] and whole number/decimal number in the case of 'Calendar'[Year].

Additionally a general rule of thumb is that you should avoid using RELATED and USERELATIONSHIP functions. They commonly cause issues in calculations and performance. 

Alternative way of doing what your DAX is trying to achieve would be e.g. this:


Data:

Relationsships:

Dax:

Measure 8 = CALCULATE(SUMX('Table (16)','Table (16)'[Quantity]*'Table (16)'[Price]),'Calendar'[Year]=2023)

Result:


No need for FILTER etc. Since the tables are connected by adding the condition [Year]=2023
we get the values for that year.

If we remove that part the result is 3000

[nemo]

Hi Valtteri,

I checked and can confirm that all the columns of the tables are of correct data types.

The Test Table used the same FILTER expession as the troublesome formula (Delivered Amount in 2007) as shown below

Thus, I suspect what you called odd is actually a bug in Power BI. Are you able to verify that by using the same troublesome formula with your data (Calendar and Table 16) to see whether you can replicate the same BLANK issue?

Thank you for a more elegant formula than the one authored by "the DAX gurus". Because your formula invokes the active relationship between the tables Date and Sales, thus it returned the Ordered Amount in 2007 instead of the Delivered Amount in 2007. I modified it with USERELATIONSHIP (from Valtteri Original = CALCULATE(SUMX('Sales','Sales'[Quantity]*'Sales'[Unit Price]), 'Date'[Calendar Year Number]=2007 to Valtteri Modified = CALCULATE([Sales Amount], USERELATIONSHIP( Sales[Delivery Date], 'Date'[Date] ), 'Date'[Calendar Year Number]=2007)

) and it returned the correct amount as shown below.

[ValtteriN]

Hi,

I wasn't able to re-create the blank values in my test file. Your modification with USERELATIONSHIP is correct that was the way I intended the new formula to be used. 

here I have 3 different ways and all return the same result:

Measure 8 = CALCULATE(SUMX('Table (16)','Table (16)'[Quantity]*'Table (16)'[Price]),'Calendar'[Year]=2023)

Measure 9 = CALCULATE(SUMX('Table (16)','Table (16)'[Quantity]*'Table (16)'[Price]),CALCULATETABLE(FILTER('Table (16)',RELATED('Calendar'[Year]) = 2023)))

Measure 10 = CALCULATE([Sales logic],FILTER(CALCULATETABLE('Table (16)',USERELATIONSHIP('Calendar'[Date],'Table (16)'[Date2])),RELATED('Calendar'[Year]) = 2023))

[nemo · Accepted solution]

Hi Valtteri,

I believe the reason you couldn't replicate the BLANK value for the troublesome formula because the relationship between 'Calendar'[Date] and 'Table (16)'[Date2] was active.

If I changed the relationship between 'Date'[Date] and 'Sales'[Delivery Date] from inactive to active, the troublesome formula would work. 

In conclusion, the BLANK value was caused by either a bug with PBI or wrong formula by Russo.

Once again, thanks for your contributions to my question.