Forum Discussion

PowerBI123456's avatar
PowerBI123456
Post Partisan
5 years ago
Solved

Measure Help

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 fo...
Need Help
Tips and Tricks
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    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's avatar
littlemojopuppy
Community Champion
5 years ago

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

PowerBI123456's avatar
PowerBI123456
Post Partisan
5 years ago

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

  • richbenmintz's avatar
    richbenmintz
    Resident Rockstar
    5 years ago

    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()
    )