Merging two datasets into one Data Table

[Anonymous]

Hey,

I am struggling with trying to return a specific type of informaion and combine into one data table.

I want a data table that shows me the following:

Technician Name	Hours Budgeted to Work	Hours Actually Worked
Bob	10	12
Larry	15	10
Cindy	10	11

The problem I am having is that I have two data sets that have this information.  Both Data Sets contain the Technician's Name, but one data set contains the Hours Budgeted and the second data set contais the Hours actually worked.  The hours actually worked has multiple time sheets like on-site time, travel time, break time, etc.  I only want the on-site time calculated as the Hours actually worked and then added to the same data table.

What function can do this?  I played around with SumX but I don't think that is it.  

Can you help?

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on your description, i created data to reproduce your scenario. The pbix file is attached in the end.

Actual:

Budget:

You may create a calculated table with the following dax.

Table = 
ADDCOLUMNS(
    Budget,
    "Hours Actually Worked",
    CALCULATE(
        SUM(Actual[Actual Value]),
        FILTER(
            Actual,
            [Name]=EARLIER(Budget[Name])&&
            [Time Sheets]="on-site time"
        )
    )
)

Result:

Best Regards

Allan

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

[AliceW]

you could create a relationship between the tables in your data model based on the technician's name (I assume it's going to be a one-to-one relationship?) Then you can display any columns in one table.

[Ashish_Mathur]

Hi,

Share the 2 datasets in a format that can be pasted in an MS Excel file.