Solved: Re: Column that search filtered values and finds o...

Anonymous · ‎04-20-2021

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!

selimovd · ‎04-20-2021

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

If you need any help please let me know.

If I answered your question please mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

View solution in original post

selimovd · ‎04-20-2021