Forum Discussion

nemo's avatar
nemo
Icon for Helper I rankHelper I
2 years ago
Solved

Russo's formula doesn't work

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!!!...
  • nemo's avatar
    nemo
    2 years ago

    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.

     

ValtteriN's avatar
ValtteriN
Icon for Community Champion rankCommunity Champion
2 years ago

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's avatar
nemo
Icon for Helper I rankHelper I
2 years ago

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.