Joining 2 x Tables with Inconsistent Data

[KCinMelbourne]

Hi guys, 

Would appreciate your advice on on combining results into a visual that is sourced from 2 tables with different fields.

Goal

Reporting a full year resource count where the source of the data is 

• Year to Date: Actual Full Time Equivalent (FTE) Table
• Rest of Year: Forecast Full Time Equivalent (FTE) Table

The outcome should look as follows assuming Jan-May is actuals and Jun - Dec is Forecast:

Problem

There are fields available in Forecast that are unique to that dataset and not found in the Actual table.  For example, "Submitted By" would indicate who submitted the forecast, but would not be relevant for actuals.  Resource Name on the otherhand is linked by a Master Table.

The current result is as follows

 

Desired Solution

I can overcome this by creating a dummy 'Submission By' field in the Actuals table and a relationship between the two but this adds added complexity to the model and generates calculated columns increasing the memory usage unnecessarily. It may seem acceptable for one field but we may have many inconsistent fields.  I was hoping to understand how I can resolve this in the measure so that the 0.6 FTE from actuals only shows against a blank line.

Thanks

KC

[v-frfei-msft]

Hi @KCinMelbourne,

I made one sampe for your reference.

1. Enter the data and append the actual and forcast table in power query.

2. Create a calculated column in the append table.

Column = SWITCH(Append1[Period],"Jun",6,"Jul",7,"Jan",1,"Feb",2,"Mar",3,"Apr",4,"May",5,"Aug",8,"Sep",9,"Oct",10,"Nov",11,"Dec",12)

For more details, please check the pbix as attached.

https://www.dropbox.com/s/ao7kyqeyfi2zoi5/join.pbix?dl=0

Regards,

Frank

[KCinMelbourne]

Thanks @v-frfei-msft for your feedback. 

I recognise the consolidation of the tables is an option but given this is part of a fully operating model, it will require some extensive effort to change and risk of dissruption to my stakeholders. 

For this reason, and also for my personal development, I was really seeking to understand specifically whether it is possible to 'ignore' the unrelated fields and return the value against 'blank' using a measure.

Cheers

Kirsty

[Stachu]

can you share how the input tables look like? 

[KCinMelbourne]

Hi @Stachu

Thanks for your message.

Following is a screenshot of the tables that would match the output tables above.  I've also attached an excel file in case that is easier to work with. 

Cheers

KC

[Stachu]

I had to create Submitted table with joins to Submitted in Forecast and inactive join to ReferenceID

Measure = 
CALCULATE(
    SUM(Fact_Actuals[FTE]),
    USERELATIONSHIP(Submitted[Submitted By],Fact_Actuals[Resource ID])
)
+ SUM(Fact_Forecast[FTE])

[KCinMelbourne]

Thanks @Stachu,

It certainly sounds likea  reasonable option.  Given I might have numerous columns though with data such as [Submitted By] I was hoping to find a solution which didn't involve a requirement to generate a 'Submitted' table as suggested.

I'm concerned that the creation of new fields would require extensive maintenance.  

I expect I may need to merge the two tables but it is a big task given the number of measures that are already written over this data so its a last resort.

Thanks for your feedback so far.

[Stachu]

apart of complexity of having more tables the maintenance is actually no effort - I referenced the Forecast table, removed all other columns, and then removed duplicates
so basically whenever forecast table is updated Submitted will be as well

I must say I don't like this solution either, but I couldn't figure out anything better

the easiest thing to do is just append the 2 tables as one, while also adding Actuals/Forecast flag column to respective tables
M will manage the inconsistent columns by itself, like in this code

let
    Source = Table.Combine({Table.AddColumn(Fact_Forecast, "Type", each "Forecast"), Table.AddColumn(Fact_Actuals, "Type", each "Actuals")})
in
    Source

[KCinMelbourne]

Thanks @Stachu,

I can certainly follow your solution as I mentioned it is my fallback option.  I am trying to understand however for my own personal development if there is a simple DAX measure solution that doesn't involve consolidating the table/creating master tables.  So I might leave my query open to see if any others have feedback.

Thanks for taking the time to investigate and respond!