Forum Discussion
RealTimeData help requested with measure to exclude values from the calculation
- 4 years ago
Ok,
So the final working measure:
KG = SUMX( SUMMARIZE( RealTimeData, RealTimeData[PrimaryKey], "RecentWeight", LASTNONBLANKVALUE(RealTimeData[RecordUpdatedOn], MAX(RealTimeData[Weight])), "RecentBin", LASTNONBLANKVALUE(RealTimeData[RecordUpdatedOn], MAX(RealTimeData[BinCode])) ), IF( [RecentBin] = CALCULATE(LASTNONBLANKVALUE(RealTimeData[RecordUpdatedOn], MAX(RealTimeData[BinCode])),ALLEXCEPT(RealTimeData,RealTimeData[PrimaryKey])), [RecentWeight], BLANK() ) )Basically it does the following:
SUMMARIZE:
Creates a grouped table, where the group is the PrimaryKey (So what's left is distinct PrimaryKey values), and add two fields to this new summarized (In memory) table.
The two fields find the last non-blank value of either the Weight or BinCode field, based on the RecordUpdatedOn field and add it as values to the summarizedtable.IF Statement:
Checks if the value from the grouped table [RecentBin] equals the Latest BinCode per (ALLEXCEPT) PrimaryKey, based on the last non-blank value in RecordUpdatedOn.
If so, then SUMX is evaluated on the [RecentWeight] field from the summarized table, and otherwise SUMX is evaluated on BLANK.Hopefully the above is helpfull for someone else.
Thanks amitchandak for your assistance that led me to this solution.
Ok,
So the final working measure:
KG =
SUMX(
SUMMARIZE(
RealTimeData,
RealTimeData[PrimaryKey],
"RecentWeight", LASTNONBLANKVALUE(RealTimeData[RecordUpdatedOn], MAX(RealTimeData[Weight])),
"RecentBin", LASTNONBLANKVALUE(RealTimeData[RecordUpdatedOn], MAX(RealTimeData[BinCode]))
),
IF(
[RecentBin] = CALCULATE(LASTNONBLANKVALUE(RealTimeData[RecordUpdatedOn], MAX(RealTimeData[BinCode])),ALLEXCEPT(RealTimeData,RealTimeData[PrimaryKey])),
[RecentWeight],
BLANK()
)
)
Basically it does the following:
SUMMARIZE:
Creates a grouped table, where the group is the PrimaryKey (So what's left is distinct PrimaryKey values), and add two fields to this new summarized (In memory) table.
The two fields find the last non-blank value of either the Weight or BinCode field, based on the RecordUpdatedOn field and add it as values to the summarizedtable.
IF Statement:
Checks if the value from the grouped table [RecentBin] equals the Latest BinCode per (ALLEXCEPT) PrimaryKey, based on the last non-blank value in RecordUpdatedOn.
If so, then SUMX is evaluated on the [RecentWeight] field from the summarized table, and otherwise SUMX is evaluated on BLANK.
Hopefully the above is helpfull for someone else.
Thanks amitchandak for your assistance that led me to this solution.