Use multiple source in the same measure

[alexcatala]

Hi, I am working on a new measure to calculate the average day to hire. 

We have a different table as one corresponds from 2016 until the end of 2019 (f_recruitment_) and then a different table from 2020 onwards( Recruitment Tracker).

We were using the following formula only used in the first table:

Avg Days To Hire =

VAR BoP_Date_Fixed = [BoP Date]

VAR f_Recruitment_Enriched =

ADDCOLUMNS (

f_Recruitment,

"Include", MAX (

[COMP_Active] * ( [OpeningDateAniversary] <= BoP_Date_Fixed ),

[NEW_Active] * ( [OpeningDateAniversary] > BoP_Date_Fixed )

)

)

VAR Result =

AVERAGEX (

FILTER (

f_Recruitment_Enriched,

[Closing date] >= [BoP Date]

&& f_Recruitment[Closing date] <= [EoP Date]

&& NOT ( ISBLANK ( [Closing date] ) )

&& NOT ( ISBLANK ( [Date Published] ) )

&& [Include] = 1

),

[Closing date] - [Date Published]

)

RETURN

IF ( Result = 0, [ND_Sign], Result )

As you could see we used (closing date), but in the new table is called (hire date) and a few other as:

1 table (Date published), 2 table (Job earliest Published Date)

I have tried to use || to add multiple choices but didn't work.

Any suggestion on how to make this measure were it could take the information from the 1 table until the end of 2019 and then from 2020 from the 2 table.

Thanks for your time!!

[amitchandak]

@alexcatala , Can you share sample data and sample output in table format?

One of the way is to merge these two by renaming common columns in power query

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

Another - Create two measure - and then put them together

[alexcatala]

Hi,

@amitchandak Yes I could append, but I will still need a measure as not all the fields are the same, as we are currently using different ones, exclude some or add some extra. That's why I was wondering if there is a way to take the data from one table until a specific time, and the rest from the new.

Any idea?

[v-shex-msft]

Hi @alexcatala,

So you mean you have multiple tables but they only recorded one part of your records? (e.g. some of them stored the 'start date', and some of them has stored records with 'end date', these tables all existed the 'relationship key' 'job id')

If this is a case, I'd like to suggest you create a calculated table to summarize these table records based on 'job id'(core fields of summary table: 'job id', 'start date', 'end date') and create an expanded table to show the detailed records of these job date ranges.

Reference link about expanding date range:

Spread revenue across period based on start and end date, slice and dase this using different dates 

Regards,

Xiaoxin Sheng