Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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.
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.
@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?
Yup, there is an account number included. Yes it is sequential.
Can you provide some sample data that includes that? Because I'd hate to assume incorrectly...
@littlemojopuppy You can assume all of these have the same account number and are in sequential order.
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()
)
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
78 | |
65 | |
60 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |