cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
alexcatala
Helper III
Helper III

Use multiple source in the same measure

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!!
 
3 REPLIES 3
amitchandak
Super User IV
Super User IV

@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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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.

Screenshot 2020-11-27 112501.pngScreenshot 2020-11-27 112342.png

 

Screenshot 2020-11-27 1132501.pngScreenshot 2020-11-27 1132145.png

 

Any idea?

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.