Forum Discussion

Quenril's avatar
Quenril
Icon for Resolver I rankResolver I
2 years ago

Related lookup column with added time component

Hello,

 

I have a 'Consultant lookup' table which contains the names of Consultants, the Practice they are in, and the date from which they were in that team. Names are repeated due to promotions or moves between Practices...

 

I then have a 'Client meetings' table which shows the activity over a given period. My dilemma: In order for my slicers to work, I need to add a column to the 'Client meetings' table to include the name of the Practice that the Consultant was in at the time that the client meeting happened - based upon the [Date of meeting] column in the 'Client meetings' table and the 'Effective from date' in the [Consultant lookup] table. I know how to use the Related formula but I don't know how to account for the changing time-element. 

 

Please can someone help? Dummy tables here:

Consultant lookup dummy.xlsx

CLIENT_MEETING_20240723070228_dummy.csv

 

Thank you

Quenril

 

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Quenril 

     

    Please try this:

    Here I add a calculated column in the 'Consultant lookup' table:

    NextData =
VAR _NextData =
    CALCULATE (
        MIN ( 'Consultant lookup'[Effective from date] ),
        FILTER (
            ALLSELECTED ( 'Consultant lookup' ),
            'Consultant lookup'[Consultant Name]
                = EARLIER ( 'Consultant lookup'[Consultant Name] )
                && 'Consultant lookup'[Effective from date]
                    > EARLIER ( 'Consultant lookup'[Effective from date] )
        )
    )
RETURN
    IF ( _NextData <> BLANK (), _NextData, TODAY () )

    Then add a calculated column in the 'Client meetings' Table:

    Column = 
	VAR _currentdate = 'Client meetings'[Date of Meeting]
	VAR _currentConsultant = 'Client meetings'[Consultant Name]
	VAR _vtable = FILTER(
		CROSSJOIN(
			SELECTCOLUMNS(
				'Client meetings',
				'Client meetings'[Date of Meeting],
				'Client meetings'[Consultant Name]
			),
			SELECTCOLUMNS(
				'Consultant lookup',
				'Consultant lookup'[Practice],
				'Consultant lookup'[Consultant Name],
				'Consultant lookup'[Effective from date],
				'Consultant lookup'[NextData]
			)
		),
		'Client meetings'[Consultant Name] = 'Consultant lookup'[Consultant Name] && 'Client meetings'[Date of Meeting] >= 'Consultant lookup'[Effective from date] && 'Client meetings'[Date of Meeting] < 'Consultant lookup'[NextData]
	)
	RETURN
		CONCATENATEX(
			FILTER(
				_vtable,
				'Client meetings'[Date of Meeting] = _currentdate && 'Client meetings'[Consultant Name] = _currentConsultant
			),
			[Practice]
		)

    The result is as follow:

     

    Best Regards

    Zhengdong Xu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Quenril's avatar
    Quenril
    Icon for Resolver I rankResolver I
    2 years ago

    Hello Zhengdong,

     

    This is so helpful and very, very close to what I need... in some instances the results includes multiple Practice names. Ideally I want only the Practice name which the Consultant is currently sat in... is there a way to remove the additional unecessariy information? I only want the last Practice information returned for each Consultant....

     

    thank you

    Quenril 

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Quenril 

       

      What does it mean that the result contains multiple practice names, and does it mean that there are several practices in a cell?

      Please provide screenshots or other forms of the instances where the results include multiple Practice names and the data that these rows refer to in the Consultant lookup and Client meetings tables.

       

      Best Regards

      Zhengdong Xu
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    2 years ago

    Hi,

    Share a single PBI file with both tables loaded there.  Why share 2 seperate Excel files?