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 the right are just to view what data we are working with).

 

Most of the values are correct, except for the ones with a red circle around them (and the corresponding Row Subtotals).

I need those values to disappear (and also don't sum in the row subtotals and grand totals (grand totals is already working)).

Basically only sum the values from the Weight column if it's the most recent record for the PrimaryKey. e.g. if a PrimaryKey has 2 records, 1 being in KOELCEL RIJ 01 and 1 in KOELCEL RIJ 02, but KOELCEL RIJ 01 has the most recent RecordUpdatedOn value, then I only need the weight from that record to show in the matrix. All records before this max RecordUpdatedOn for the same PrimaryKey should NOT show in the matrix.

I've been trying a whole lot different stuff for almost the entire day, but I can't seem to figure it out... 

 

In normal datasets I would use the Earlier function or I would handle this in the Query Editor, but as this is RealTimeData from a Streaming Dataset, I can only use measures in my report.

 

Hopefully one of you masters can help me out here.

 

Need Help
  • bcadmin_powerbi's avatar
    bcadmin_powerbi
    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.

1 Reply

  • bcadmin_powerbi's avatar
    bcadmin_powerbi
    Regular Visitor
    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.