I have a DAX formula that is performing really bad and hopefully someone here can suggest a solution.
I have a table that contains about 400000 rows of data. ProductID's (example field), startdate, enddate and an IsActive flag field. The data out of this table should be reported in several ways. In some reports I want to see all of the active products within a selected period of time and in other reports, I only want to see the number of products that were active on the last day of the month.
So, I have created two DAX queries to calculate this.
First I calculate the active products:
_Calc_Count Fields :=
CALCULATE (
DISTINCTCOUNT ( MyFactTable[ProductID] ),
FILTER (
MyFactTable,
MyFactTable[StartDate] <= CALCULATE ( MAX ( 'Date'[Date] ) )
&& MyFactTable[EndDate] >= CALCULATE ( MIN ( 'Date'[Date] ) )
),
MyFactTable[IsActive] = 1
)
Please be aware of the fact that the report this calculation is used in can also contain a date range (even a whole year (or multiple years) can be selected with a startdate and enddate selected in the filter). The report also slices on other filters like Client Group.
Then I have a second calculation that uses the first one and applies the LASTNONBLANK function:
Last Non Blank Value :=
CALCULATE (
[_Calc_Count Fields],
LASTNONBLANK ( 'Date'[Date], [_Calc_Count Fields] )
)
Both calculations are very, very slow.
Can anyone suggest a better approach? Can the DAX formula be optimized or should it completely be rewritten?
ps. I am using Analysis Services Tabular Model.
Thank you all in advance for your responses!