Hello developers,
Our team recently create a measure to get latest value.
The dax expression we are using is as follows:
Latest Error =
SUMX (
SELECTCOLUMNS (
SUMMARIZE (
BE_PROCESS,
BE_PROCESS[COL1],
BE_PROCESS[COL2],
BE_PROCESS[COL3],
"Latest Value", CALCULATE (
MAX ( BE_PROCESS[Error] ),
FILTER ( BE_PROCESS, BE_PROCESS[TIMESTAMP] = MAX ( BE_PROCESS[TIMESTAMP] ) )
)
),
"latest value", [Latest Value]
),
[latest value]
)Since our user is only interested with the latest error they have so we first use summerize to create a group by table and then get error value with the largest timestamp.
The challenge here is this measure is taking too long to display the visuals especially when user apply different filters to the report. (The summerize expression has more columns and we have almost 40 million records in our ssas cube)
Initially we are using a calculated column with calculate and earlier function. However, this doesn't fit our requirement.
The use case is that we have a filter on the report called users. If only one user is selected or filtered, it should give you the max timestamp for that specific user. If two or more users are slected, it should give you the max timestamp among these users:
User Timestamp
A 100
A 150
B 200
B 150
If we use sum(error) it will aggregate all these 4 records;
If we use ealier and calculate function to create calcualted column it will aggregate A 150 and B 200;
If we use summarize it will caculate only B 200 (this is what we want).
Any idea or suggestion if we can optimize the above expression? Or any other dax expression could get the same reulst?
Thanks much!
Jason
