Power BI Dax Linking data from multiple tables

[HYEasterly]

I'm missing something... and don't know what... I can't get it to give me what I want...

How do I get my table to give me the equipment run time and the billable work for each day and each foreman?

Desired Results:

Date            Foreman Name         Equipment ID       Billable Amt       Equipment Run Time

August 25     John Doe                  9876                     $5,090                 5.5 hours

August 25     Robert Smith            6543                     $3,088                 4.5 hours

Description of Data:

Date Table contains

       DateKey (each date listed once)

Equipment Table contains

       Equipment ID (each equipment listed once)

       Equipment Description

Users Table contains

       Employee ID (Each employee listed once)

       Employee Name

Track Invoice Table contains

      Work Date

      Foreman ID

      Billable Work

Track Equipment Table contains

      Work Date

      Foreman ID

      Equipment ID

GPS Table contains

     Date

     Equipment ID

     Run Time

Relationships:

Date Table[DateKey] = GPS Table[Date] 

Date Table[DateKey] = Track Invoice Table[Work Date]

Date Table[DateKey] = Track Equipment Table[Work Date]

Users Table[Employee ID] = Track Invoice Table[Foreman ID]

Users Table[Employee ID] = Track Equipment Table[Foreman ID]

Equipment Table[Equipment ID] = GPS Table [Equipment ID]

Equipment Table[Equipment ID] = Track Equipment Table[Equipment ID]

Issues:

I have a measure that calculates the billable work.  When I pull the Date Table[DateKey] and Users[Employee Name] and this measure it will give me the Billable Work Amount correctly.

I have tried several measures to get the run time but nothing is working correctly... 

Your help is appreciated!

[danextian]

Hi @HYEasterly 

You've showed the desired result but didn't provide us with a sample data. There's also no mention of how to compute runtime. Your expected result breaks it down by employee but GPS table doesnt have this information.

Please provide a workable sample data (not an image), your expected result from the same sample data and your reasoning behind. You may post a link to Excel or a sanitized copy of your PBIX stored in the cloud.

[Shahid12523 · Accepted solution]

Billable Work (works):

Billable Work = SUM ( 'Track Invoice'[Billable Work] )

Run Time (if direct relationships work):

Run Time = SUM ( 'GPS'[Run Time] )

If Foreman doesn’t filter GPS → use TREATAS bridge:

Run Time =
CALCULATE (
SUM ( 'GPS'[Run Time] ),
TREATAS ( VALUES ( 'Track Equipment'[Equipment ID] ), 'GPS'[Equipment ID] ),
TREATAS ( VALUES ( 'Track Equipment'[Work Date] ), 'GPS'[Date] )
)

Use the TREATAS version if your runtime doesn’t align with Foreman/Date/Equipment.

[HYEasterly]

Thank you!  This worked.  I had not used the treat as function before.  Do you have any insight on when Treat As is needed versus not?  Just curious, as I couldn't figure out why my data didn't flow better.  

Giving a sample of data as requested by others, sounds sensible... however, by the time I build a sample set of data... it works the way I want it to... not the way my actual data is working...  so your suggestion is greatly appreciated!  Better yet... IT WORKED!!

THANK YOU!

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...