Snow non-blank latest row data in a matrix table

[subhendude]

I have a dataset containing machine inventory details, including owner information and power consumption. When a machine stops, the owner field is left blank, and power consumption is set to zero. For instance, Machine 4 stopped on May 2nd, which is why its owner field is blank and its consumption is zero. Similarly, Machine 5 stopped on May 3rd, resulting in a blank owner field and zero consumption.

When I plot the data in a matrix, the owner field is repeated twice for Machine 4 and Machine 5, likely due to two variations in the owner data.

I would like to display the most recent non-blank owner information for these machines in the Owner field. For instance, Machine 4 should show Owner4, and Machine 5 should show Owner5 like below

Can anyone sugesst how to show this? Any help is really appreciated.

Thanks!

[Ashish_Mathur]

Hi,

In the Query Editor, use the "Fill down" feature on the Owner column

[Ritaf1983 · Accepted solution]

Hi @subhendude 
Maybe it is not the most elegant way, but it works 🙂

You can create 2 calculated columns :

1. For last nonblank date for every machine :

LastNonBlankDate =

VAR CurrentMachine = [Machine]

VAR CurrentDate = [Date]

RETURN

    CALCULATE(

        MAX('Table'[Date]),

        FILTER(

            'Table',

            'Table'[Machine] = CurrentMachine &&

            'Table'[Owner] <> BLANK() &&

            'Table'[Consumption] > 0 &&

            'Table'[Date] <= CurrentDate

        )

    )

2. For the owner in this date

LastOwnerOnDate =

VAR CurrentMachine = [Machine]

VAR LastNonBlankDate = [LastNonBlankDate]

RETURN

    CALCULATE(

        MAX('Table'[Owner]),

        FILTER(

            'Table',

            'Table'[Machine] = CurrentMachine &&

            'Table'[Date] = LastNonBlankDate

        )

    )

Now, you can use the calculated owner in your visuals:

The pbix is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.