Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a table of outlets visits done by sales representatives. This table is built like this:
VisitId | Date | OutletId | AgentId | User Role
There are two types of Agents, those with User Role = 3 ("agent 3") that do regular visits and those with User Role = 4 ("agent 4") that audit the visits done by agents with User Role = 3.
For every visit done in an outlet by an agent 4 I want to find the most recent visit done by an agent 3 on the same outlet that occurred before the agent 4 visit.
This is a sample of the table of data I am using.
VisitId | Date Visit | OutletId | AgentId | User Role | Column |
121630 | 18/02/2021 14:47 | 9117 | 108 | 4 | 120982 |
138735 | 24/03/2021 11:26 | 9117 | 89 | 3 | blank |
135467 | 17/03/2021 13:07 | 9117 | 89 | 3 | blank |
124671 | 24/02/2021 16:09 | 9117 | 89 | 3 | blank |
120982 | 17/02/2021 12:52 | 9117 | 89 | 3 | blank |
113596 | 03/02/2021 13:18 | 9117 | 89 | 3 | blank |
107010 | 20/01/2021 13:46 | 9117 | 89 | 3 | blank |
103601 | 13/01/2021 13:10 | 9117 | 89 | 3 | blank |
98454 | 30/12/2020 12:54 | 9117 | 89 | 3 | blank |
88501 | 02/12/2020 14:28 | 9117 | 89 | 3 | blank |
83027 | 18/11/2020 13:48 | 9117 | 89 | 3 | blank |
135707 | 18/03/2021 13:52 | 9118 | 102 | 4 | 135435 |
129405 | 05/03/2021 15:02 | 9118 | 102 | 4 | 128365 |
126410 | 27/02/2021 13:13 | 9118 | 102 | 4 | 124774 |
117662 | 11/02/2021 14:32 | 9118 | 102 | 4 | 117274 |
138998 | 24/03/2021 13:30 | 9118 | 83 | 3 | blank |
135435 | 17/03/2021 16:37 | 9118 | 83 | 3 | blank |
131916 | 10/03/2021 13:46 | 9118 | 83 | 3 | blank |
128365 | 03/03/2021 16:42 | 9118 | 83 | 3 | blank |
124774 | 24/02/2021 16:33 | 9118 | 83 | 3 | blank |
120922 | 17/02/2021 15:42 | 9118 | 83 | 3 | blank |
117274 | 10/02/2021 13:56 | 9118 | 83 | 3 | blank |
Column is the desired calculated column that I am trying to build. As you can see, only the rows with User Role = 4 should have an output, and it would go to take from the VisitId column search for the same OutletId and find the VisitId that done by User Role = 3 users and take the one with DATEDIFF that is the smallest.
The only column I was able to do got me an output that is wrong since I am able to select only the most recent visit done by an agent 3 and associate it to visit done by agent 4 done within 3 days.
Column =
var MaxVisitAgent = IF(Visits[Users.Role] = 4, CALCULATE(MAX(Visits[Id]), FILTER(ALLEXCEPT(Visits, Visits[OutletId]), Visits[Users.Role] = 3)))
var DateVisitAgent = IF(Visits[Users.Role] = 4, CALCULATE(MAX(Visits[BeginDate]), FILTER(ALLEXCEPT(Visits, Visits[OutletId]), Visits[Users.Role] = 3)))
RETURN
IF(DATEDIFF(Visits[BeginDate], DateVisitAgent, DAY) < 3, MaxVisitAgent)
Any help is very appreciated!
Solved! Go to Solution.
Hey @Anonymous ,
ok, that was a little tricky, but I think I figured it out.
Check the following calculated column:
Closest VisitId =
VAR vRowOutletID = myTable[OutletId]
VAR vDateVisit = myTable[Date Visit]
VAR vTable = ADDCOLUMNS(
CALCULATETABLE(
myTable,
myTable[User Role] = 3,
myTable[OutletId] = vRowOutletID,
myTable[Date Visit] <= vDateVisit,
myTable
),
"@DateDiff", DATEDIFF( myTable[Date Visit], vDateVisit, DAY )
)
VAR vMinDiff = MINX( vTable, [@DateDiff] )
RETURN
IF(
myTable[User Role] = 3,
BLANK(),
CALCULATE(
MAX( myTable[VisitId] ),
FILTER( vTable, [@DateDiff] = vMinDiff )
)
)
Hey @Anonymous ,
ok, that was a little tricky, but I think I figured it out.
Check the following calculated column:
Closest VisitId =
VAR vRowOutletID = myTable[OutletId]
VAR vDateVisit = myTable[Date Visit]
VAR vTable = ADDCOLUMNS(
CALCULATETABLE(
myTable,
myTable[User Role] = 3,
myTable[OutletId] = vRowOutletID,
myTable[Date Visit] <= vDateVisit,
myTable
),
"@DateDiff", DATEDIFF( myTable[Date Visit], vDateVisit, DAY )
)
VAR vMinDiff = MINX( vTable, [@DateDiff] )
RETURN
IF(
myTable[User Role] = 3,
BLANK(),
CALCULATE(
MAX( myTable[VisitId] ),
FILTER( vTable, [@DateDiff] = vMinDiff )
)
)
Thank you it works!
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |