cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Last value of a group

Hi community,

I have de following dataset: 

idstepval1val2
101155
101285
1013 10
101438
1015 15
1021 9
102227
1023 5
1024713
1025 8

 

I need get a value of val2 where val1 is not null, for each ID, respecting the step. In example above, I need value 8 for ID 101 and 13 for ID 102. I tried use lastnoblank in a matrix with ID in rows but does not worked.

 

Basically, the result that I need in this example, is the matrix below:

IDVal1Val2Diff
101385
1027136

 

Anybody help me in this case?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Champion
Community Champion

Re: Last value of a group

Here is a measure expression that calculates your different directly.  If you want to return Val1 and Val2, just return those variables instead of the difference.

 

Difference =
VAR __maxstepnotnull =
    CALCULATE ( MAX ( val[step] ), NOT ( ISBLANK ( val[val1] ) ) )
VAR __val1 =
    CALCULATE ( MIN ( val[val1] ), val[step] = __maxstepnotnull )
VAR __val2 =
    CALCULATE ( MIN ( val[val2] ), val[step] = __maxstepnotnull )
RETURN
    __val2 - __val1

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

View solution in original post

1 REPLY 1
Highlighted
Community Champion
Community Champion

Re: Last value of a group

Here is a measure expression that calculates your different directly.  If you want to return Val1 and Val2, just return those variables instead of the difference.

 

Difference =
VAR __maxstepnotnull =
    CALCULATE ( MAX ( val[step] ), NOT ( ISBLANK ( val[val1] ) ) )
VAR __val1 =
    CALCULATE ( MIN ( val[val1] ), val[step] = __maxstepnotnull )
VAR __val2 =
    CALCULATE ( MIN ( val[val2] ), val[step] = __maxstepnotnull )
RETURN
    __val2 - __val1

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

View solution in original post

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors