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 for each of 2 respones would be:

 Any tips?

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.

6 Replies

  • littlemojopuppy's avatar
    littlemojopuppy
    Community Champion
    5 years ago

    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's avatar
      PowerBI123456
      Post Partisan
      5 years ago

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

      • 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...

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    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.