Filtering FIRSTNONBLANKVALUE - can't seem to figure it out.

[oliverwebb]

Hi all,

I have a table in my Dataset that has:

• Four columns of unique data
• Two additional columns
  • Date
  • Area

There are other columns that come from the query but I don't need those. It looks like this (I've filtered it down to only two locations for ease of screenshotting):

What I'm having a problem with is filtering the following down to the first non-blank value:

• Rate per 100k (last 7 days rolling)
• Cases Last 7 Days

I managed at one point to return the date of the first non-blank value, but I got stumped there and couldn't do anything else. I even tried to use the Measure that returned the date to filter but I couldn't get that to work either.

I found an article online which seems to do what I want, and wrote the following:

Latest_Rate_Per_100k_Rolling = 
VAR LastNonBlankDate =
 CALCULATE (
 LASTNONBLANK ( LTLA[date], 1 ),
 FILTER (
 ALL ( LTLA ),
 LTLA[date] <= EARLIER ( LTLA[date] )
 && NOT ( ISBLANK ( LTLA[Rate per 100k (last 7 days rolling)] ) )
 )
 )
RETURN
 CALCULATE (
 SUM ( LTLA[Rate per 100k (last 7 days rolling)] ),
 FILTER ( ALL ( LTLA ), LTLA[date] = LastNonBlankDate )
 )

But this gives me the error:

EARLIER/EARLIEST refers to an earlier row context which doesn't exist.

Any ideas on how to do this?

[amitchandak]

@oliverwebb , if this measure use max

Latest_Rate_Per_100k_Rolling = 
VAR LastNonBlankDate =
 CALCULATE (
 LASTNONBLANK ( LTLA[date], 1 ),
 FILTER (
 ALL ( LTLA ),
 LTLA[date] <= MAX( LTLA[date] )
 && NOT ( ISBLANK ( LTLA[Rate per 100k (last 7 days rolling)] ) )
 )
 )
RETURN
 CALCULATE (
 SUM ( LTLA[Rate per 100k (last 7 days rolling)] ),
 FILTER ( ALL ( LTLA ), LTLA[date] = LastNonBlankDate )
 )

Also, check if lastnonblankvalue can helphttps://www.linkedin.com/pulse/five-recent-power-bi-functions-you-should-use-more-often-amit-chandak

[oliverwebb]

This doesn't seem to work - it returns 637679.5 but if I check the table it should be 1195.7 for the latest record.

[v-shex-msft · Accepted solution]

Hi @oliverwebb,

You can try to use the following measure format to get the last value and return tags, then you can use it on 'visual level filter' to filter records based on tags:

Latest Rate_Per_100k_Rolling =
VAR currDate =
    MAX ( LTLA[date] )
VAR _lastDate =
    CALCULATE (
        MAX ( LTLA[date] ),
        FILTER (
            ALLSELECTED ( LTLA ),
            LTLA[date] <= currDate
                && LTLA[Rate per 100k (last 7 days rolling)] > 0
        ),
        VALUES ( LTLA[AreaCode] )
    )
VAR lastValue =
    CALCULATE (
        MAX ( LTLA[Rate per 100k (last 7 days rolling)] ),
        FILTER ( ALLSELECTED ( LTLA ), LTLA[date] = _lastDate ),
        VALUES ( LTLA[AreaCode] )
    )
RETURN
    IF ( MAX ( LTLA[Rate per 100k (last 7 days rolling)] ) = lastValue, "Y", "N" )

Applying a measure filter in Power BI - SQLBI

Regards,

Xiaoxin Sheng