Forum Discussion
Helper IRusso's formula doesn't work
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.
Helper IHi 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?
Community ChampionHi,
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:
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
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.