Measure Help

[PowerBI123456]

If possible in a measure, I am trying to determine who the requester is on a particular response based on the date. 

For example, my data looks like:

Based on the data, the requester for each of 2 respones would be:

 Any tips?

[v-alq-msft · Accepted solution]

Hi, @PowerBI123456 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may go to 'Query Editor' and add an index column.

Then you may create a measure as below.

Requestor = 
var maxindex =
CALCULATE(
    MAX('Table'[Index]),
    FILTER(
        ALL('Table'),
        [Date]<MAX('Table'[Date])&&
        [Action]="Request"
    )
)
return
IF(
    MAX('Table'[Action])="Response",
    MAXX(
        FILTER(
            ALL('Table'),
            [Index]=maxindex
        ),
        [User]
    )
)

Result:

Best Regards

Allan

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

[littlemojopuppy]

@PowerBI123456 this could be either very easy or very difficult.

How are you determining what someone is the Requestor of?  Is there a ticket number or something (not included in data)?  Is it sequentially...and what if the data happens to be in a different order?

[PowerBI123456]

Yup, there is an account number included.  Yes it is sequential. 

[littlemojopuppy]

Can you provide some sample data that includes that?  Because I'd hate to assume incorrectly...

[PowerBI123456]

@littlemojopuppy  You can assume all of these have the same account number and are in sequential order. 

[richbenmintz]

Hi @PowerBI123456 ,

try the following custom column

Requestor = 
    var _date = [Date]
return
if(
    [Action] = "Response", 
        CALCULATE(MAX('Table'[User]),TOPN(1, FILTER('Table', 'Table'[Date] < _date && [Action] = "Request"), [Date], DESC))
    , BLANK()
    )