Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Column that search filtered values and finds one with smallest difference to row

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!

1 ACCEPTED SOLUTION
selimovd
Super User
Super User

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
 

View solution in original post

2 REPLIES 2
selimovd
Super User
Super User

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
 
Anonymous
Not applicable

Thank you it works!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.