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.
Community ChampionHi,
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/
Helper IHi 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