cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

LOOKUPVALUE in range of dates

Dear all,

 

I have two tables:

 

TABLE_1, that has a list of all employees on each month (below, just an example with one employee_id)

 

EMPLOYEE_IDMONTHLY PERIOD
287510/01/2016
287511/01/2016
287507/01/2017
287508/01/2017

 

TABLE_2 that has information for each employee of absenses (example: vacations, medical leave, pregnancy, etc).

 

EMPLOYEE_IDSTARTENDTYPE
287510/15/201611/05/2016120 (VACATIONS)
287507/20/201708/10/2017290 (MEDICAL LEAVE)

 

I´m trying to add a calculated column in TABLE_1 so I would have, for each period, what was the condition of the employee.

The conditions would be:

"IF (TABLE_2[START] < ENDOFMONTH(TABLE_1[MONTHLY PERIOD])

&& TABLE_2[END] > ENDOFMONTH(TABLE_1[MONTHLY PERIOD]))

THEN TABLE_2[TYPE]" <-- of that specific line

ELSE "ACTIVE"

 

The TABLE_1 would result in the below:

 

EMPLOYEE_IDMONTHLY PERIODTYPE
287510/01/2016120 (VACATIONS)
287511/01/2016ACTIVE
287507/01/2017290 (MEDICAL LEAVE)
287508/01/2017ACTIVE

 

Ideas on how to do it would be appreciated!

3 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: LOOKUPVALUE in range of dates

Hey,

 

here you will find pbix file and here the sample data

 

There is a new table "Sheet3" that represents the table with the uniqe "Employee_ID". From my understanding the relationships should look like this

Data Modeling - Relationships.png

 

There is a new measure in Sheet1

Measure 2 = 
IF(ISFILTERED(Sheet1[EMPLOYEE_ID])
,CALCULATE(
    CONCATENATEX(
            SELECTCOLUMNS('Sheet1', "EMPLOYEE_ID", 'Sheet1'[EMPLOYEE_ID], "MONTHLY_PERIOD", 'Sheet1'[MONTHLY PERIOD])
            ,var currentEmployee = [EMPLOYEE_ID] 
            var currentMonthlyPeriod = CALCULATE(EOMONTH(MAX('Sheet1'[MONTHLY PERIOD]),0)) 
            var empType = 
			CONCATENATEX('Sheet2'
                ,IF('Sheet2'[EMPLOYEE_ID] = currentEmployee 
			     && 'Sheet2'[START] < currentMonthlyPeriod 
			     && 'Sheet2'[END] > currentMonthlyPeriod
			     ,'Sheet2'[TYPE]
			     ,BLANK()
                )) 
			return
            IF(empType = "","ACTIVE",empType)
			) 
        )
)

and also a column in Sheet1, that is basically the same as the measure except the 1st check if Sheet1[Employee_ID] is filtered.

Dependig on the table size of your "Sheet1", I would recommend to use the column version if the table size is small (what ever that means) ;-), this is due to memory consumption of "physical / calculated columns".

 

Regards 

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
Super User
Super User

Re: LOOKUPVALUE in range of dates

Hey @Anonymous,

 

if you have some time, you may redownload the pbix file. There are two new calculated columns: column 3 and column 4.

 

If you are sure that there is just one daterange in table2 for any given date from table1 try column4, else go with column3.

Column3 is a little optimized in comparison to the initial column whereas Column 4 assumes just one valid daterange for any given employee / period.

 

I have to admit that I'm somewhat intereested if there a performance gain using the new columns

 

Happy to help

 

Regards

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
Anonymous
Not applicable

Re: LOOKUPVALUE in range of dates

Tom,

 

I tested all of them. Column 4 is perfect for me, and it was extremely fast.

 

In my data set:

Column took 32 seconds

Column 2 took more than 3 minutes and I killed it

Column 3 took more than 3 minutes and I killed it

Column 4 took 3 seconds

 

I´m in a Dell M3800, i7 with 16gb RAM.

12 REPLIES 12
Anonymous
Not applicable

Re: LOOKUPVALUE in range of dates

It looks like what I´m trying to do is a "many to many relationship", isn´t it? I tried to read the articles on it and learn, but still having difficulties.

Highlighted
Super User
Super User

Re: LOOKUPVALUE in range of dates

Hey,

 

using your sample data, I came up with this measure

 

Measure = 
IF(ISFILTERED('Sheet1'[EMPLOYEE_ID])
,IF(
CONCATENATEX(	'Sheet1'
	,var currentEmployee = 'Sheet1'[EMPLOYEE_ID]
	var currentDate = CALCULATE(EOMONTH(MAX('Sheet1'[MONTHLY PERIOD]),0))
	return
	CONCATENATEX('Sheet2'
		,IF('Sheet2'[EMPLOYEE_ID] = currentEmployee 
			&& 'Sheet2'[START] < currentDate 
			&& 'Sheet2'[END] > currentDate
			,'Sheet2'[TYPE]
			,BLANK()
		)
	)
)
<>""
,CONCATENATEX(	'Sheet1'
	,var currentEmployee = 'Sheet1'[EMPLOYEE_ID]
	var currentDate = CALCULATE(EOMONTH(MAX('Sheet1'[MONTHLY PERIOD]),0))
	return
	CONCATENATEX('Sheet2'
		,IF('Sheet2'[EMPLOYEE_ID] = currentEmployee 
			&& 'Sheet2'[START] < currentDate 
			&& 'Sheet2'[END] > currentDate
			,'Sheet2'[TYPE]
			,BLANK()
		)
	)
)
,"ACTIVE"
))

 

This measure creates this output

 

 

Lookup Value - result.png 

 

Hope this helps, currently I've been in a hurry so there may be potential for some perfomance tweeks.

 

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
Anonymous
Not applicable

Re: LOOKUPVALUE in range of dates

Hi Tom,

The measure worked and it´s pretty close to the solution.

However, somehow my slicers are not filtering the result. I tried to add some "ALLEXCEPT" in the formula, but wasn´t able to get it right.

 

I´m trying to "slice it" through 'Sheet2'[EMPLOYEE_ID]

 

Thank you

Super User
Super User

Re: LOOKUPVALUE in range of dates

Hey Eduardo,

 

I'm wondering how your datamodel looks like and if there is any relationship between your tables Table1 and Table2, may it direct or indirect (e.g. a table that relates to both tables - 'emplyoee'.

 

How do you try to achieve slice table1 by a column from table2 if both tables are not related, can you please mock a report and share an image with some annotations.

 

Wondering what do you expect, when a user selectcs 2 id's or none.

 

Regards

 

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
Anonymous
Not applicable

Re: LOOKUPVALUE in range of dates

Tom,

 

You are correct on the indirect relationship. It looks like this:

 

image.pngTABLE_1 = SAP_HR-VW_Z_FOLHA and TABLE_2 = SAP_HR-Z_PA2001

 

The indirect relation is a table that contains distinct employee_ids, with all sort of information from the person (date of birth, address, gender, date of admission, date of dismissal, etc).

 

What I´m trying to do is "select a person" (a name, from this indirect relation), and have the employee_ID filtered on both TABLE_1 and TABLE_2.

 

image.pngIf I try to slice through this indirect relation, see that both employee_ids (left and right) are filtered, but not the result of the measure below.image.pngIf I slice through "TABLE_2[EMPLOYEE_ID" nothing happens.image.pngFinally, If I slice it through "TABLE_1[EMPLOYEE_ID]" I get the expected result.

Anonymous
Not applicable

Re: LOOKUPVALUE in range of dates

Could this be a calculated column instead of a measure?

Super User
Super User

Re: LOOKUPVALUE in range of dates

Hey,

 

here you will find pbix file and here the sample data

 

There is a new table "Sheet3" that represents the table with the uniqe "Employee_ID". From my understanding the relationships should look like this

Data Modeling - Relationships.png

 

There is a new measure in Sheet1

Measure 2 = 
IF(ISFILTERED(Sheet1[EMPLOYEE_ID])
,CALCULATE(
    CONCATENATEX(
            SELECTCOLUMNS('Sheet1', "EMPLOYEE_ID", 'Sheet1'[EMPLOYEE_ID], "MONTHLY_PERIOD", 'Sheet1'[MONTHLY PERIOD])
            ,var currentEmployee = [EMPLOYEE_ID] 
            var currentMonthlyPeriod = CALCULATE(EOMONTH(MAX('Sheet1'[MONTHLY PERIOD]),0)) 
            var empType = 
			CONCATENATEX('Sheet2'
                ,IF('Sheet2'[EMPLOYEE_ID] = currentEmployee 
			     && 'Sheet2'[START] < currentMonthlyPeriod 
			     && 'Sheet2'[END] > currentMonthlyPeriod
			     ,'Sheet2'[TYPE]
			     ,BLANK()
                )) 
			return
            IF(empType = "","ACTIVE",empType)
			) 
        )
)

and also a column in Sheet1, that is basically the same as the measure except the 1st check if Sheet1[Employee_ID] is filtered.

Dependig on the table size of your "Sheet1", I would recommend to use the column version if the table size is small (what ever that means) ;-), this is due to memory consumption of "physical / calculated columns".

 

Regards 

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
Anonymous
Not applicable

Re: LOOKUPVALUE in range of dates

Dear Tom,

 

Thank you very much for your time. It worked.

I had to go with the measure 2. The column took more than 15 minutes to update , and it didn´t! I had to kill the PBI process (and I do have a nice computer). The data is not that big, but I guess the number of iteractions are: in "TABLE_1" I have 186,822 rows, and "TABLE_2" 108,576 rows.

 

Best regards,

Eduardo

 

Anonymous
Not applicable

Re: LOOKUPVALUE in range of dates

Actually when I killed the process, reopened, and try to add the column again, it worked after about a minute.

So, solution fully accepted!

Best regards,

Eduardo