Forum Discussion
Performance Between Two Measures - How to make the slower one more performant?
Just a question about what you are trying to accomplish.
Doesn't this section of the code:
CROSSJOIN(
CALCULATETABLE(
'*ACM Cylinder History',
'*ACM Cylinder History'[Date/Time - Last Modified] <> BLANK()
),
DATATABLE(
"Quantity", INTEGER,
{
{-1},
{1}
}
)
),
Always return a 0? You are summing the [Quantity] which, for every row where the
'*ACM Cylinder History'[Date/Time - Last Modified] <> BLANK() is both 1 and -1 which makes the SUM 0 right?
When I tested this code
Running Balance New =
CALCULATE (
SUMX ( '*ACM Cylinder History', 1 ),
'*ACM Cylinder History'[Date/Time - Last Modified] = BLANK (),
REMOVEFILTERS ( 'Date Dimension' ),
REMOVEFILTERS ( '*ACM Cylinder History'[Date - Transaction] ),
FILTER (
CALCULATETABLE ( RELATEDTABLE ( 'Date Dimension' ), ALL () ),
ISONORAFTER ( 'Date Dimension'[Date], MAX ( 'Date Dimension'[Date] ), DESC )
)
)
I get the same results as your original but it is much faster.
A table with the date and just the [Running Balance New] calcs in around 100 - 120 ms
Hi jdbuchanan71,
You are correct, that will always equal 0. That is a bug in my DAX code, and I realize that fixes it complicates the measure further. The correct forumla goes as follows:
IF [Date/Time - Last Modified] <> BLANK()
THEN
IF Quantity = 1
THEN
[Date/Time - Date Dimension Key] = [Date/Time - Last Modified]
ELSE
[Date/Time - Date Dimension Key] = [Date/Time - Modified]
ELSE
[Date/Time - Date Dimension Key] = [Date/Time - Modified]
The reason there are these conditions are because the table contains rows from two different sources, and the dates mean slightly different things.
Regardless, that is the formula so if it was correct then I don't think your simplification will work. Thinking about how to express the above in DAX...
Thank you,
Anthony
- jdbuchanan715 years ago
Super User
Sorry, it's not really clear to me where you want to incorporate the fix. Are you saying that should be a column on the '*ACM Cylinder History' table then you join that one into your 'Date Dimension' table?