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.
I have an activity log and a log of performance visits. I am essentially trying to assign each activity in the log a visit based on the filters below. Specifically, I am trying to find the performance visit that occured most recently before the activity. I managed to calculate the date and have that in a column but need the ID for the associated visit as well in another column. The equation for the date is:
Last PV Before Activity = CALCULATE( MAX('History of Performance Visits'[Date of Visit]), FILTER('History of Performance Visits','History of Performance Visits'[Date of Visit] < Activity[Date]), FILTER('History of Performance Visits', 'History of Performance Visits'[aM Dealer Number] = Activity[aM Dealer Number]) )
This is what I have so far to find the ID:
Last PV ID Before Activity = VAR datematch = FILTER('History of Performance Visits','History of Performance Visits'[Date of Visit] < Activity[Date]) VAR dealermatch = FILTER('History of Performance Visits', 'History of Performance Visits'[aM Dealer Number] = Activity[aM Dealer Number]) VAR maxdate = MAX('History of Performance Visits'[Date of Visit]) RETURN LOOKUPVALUE('History of Performance Visits'[PV ID], 'History of Performance Visits'[Date of Visit], maxdate, 'History of Performance Visits'[aM Dealer Number],dealermatch, 'History of Performance Visits'[Date of Visit],datematch)
Solved! Go to Solution.
Hi @Anonymous ,
You can try to use following formula if it works:
Last PV ID = VAR _filtered = FILTER ( ALL ( Visits ), [Date of Visit] < EARLIER ( Activity[Date] ) && Visits[aM Dealer Number] = EARLIER ( Activity[aM Dealer Number] ) ) VAR _lastdate = MAXX ( _filtered, [Date of Visit] ) RETURN MAXX ( FILTER ( _filtered, [Date of Visit] = _lastdate ), [PV ID] )
Regards,
Xiaoxin Sheng
Hi @Anonymous,
Please share us some sample data with expected result so that we can test to coding formula on it.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
Activity Table:
Visits Table:
in the rightmost column of the activity table is where I am trying to pull in the answer. For example, lets say there was a visit (ID: ABC) on 1/1 and another visit (ID: DEF) on 1/5. There was activity on 1/2, 1/3, and 1/4, all 3 of those activities should have "ABC" in the right most column. I am able to calc the date using the first equation above but can't retrieve the ID.
Hi @Anonymous ,
You can try to use following formula if it works:
Last PV ID = VAR _filtered = FILTER ( ALL ( Visits ), [Date of Visit] < EARLIER ( Activity[Date] ) && Visits[aM Dealer Number] = EARLIER ( Activity[aM Dealer Number] ) ) VAR _lastdate = MAXX ( _filtered, [Date of Visit] ) RETURN MAXX ( FILTER ( _filtered, [Date of Visit] = _lastdate ), [PV ID] )
Regards,
Xiaoxin Sheng
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |