Forum Discussion

bcadmin_powerbi's avatar
bcadmin_powerbi
Regular Visitor
4 years ago
Solved

RealTimeData help requested with measure to exclude values from the calculation

Refer to the below screenshot of my .pbix.   What I'm trying to achieve here is that only the most recent records that are in my realtimedata dataset are shown in the matrix on the left. (Tables on...
  • bcadmin_powerbi's avatar
    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.