Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Merging two datasets into one Data Table

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 NameHours Budgeted to WorkHours Actually Worked
Bob1012
Larry15

10

Cindy1011

 

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?

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    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.

3 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    Hi,

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

  • AliceW's avatar
    AliceW
    Power Participant
    5 years ago

    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.

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    5 years ago

    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.