How to Structure Data Using Two Fact Tables

[rlussky]

Hello, I have two fact tables I'd like to use for analysis that looks similar to this:

Where:
* The Transactions table has a record for every transaction. 

* The Hours Worked table has a record for every timecard in/out punch.

I'd like data as granular as by date and minute by employee. The only way I can think to do this is to create a summary table with a record for every employee and date/time instance. I'd have binary flags for whether they were clocked in and whether they had a transaction in that time frame. 

1) I'm not sure how I can accomplish this structure. 2) There has to be a better way to do this.

Any insight would be appreciated!

[v-jingzhang]

Hi @rlussky 

Do you want to have a new table to summary these data or a table visual to display these data in a report? If you want to have a new table, you can create a calculated table with SUMMARIZE or SUMMARIZECOLUMNS function. Are the Clocked In and Transaction columns already existing in two fact tables or we need to create formulas for them? 

Otherwise, if you want to have a table visual in the report, you can drag Employeed ID column from Employee table and DateTime column from DateTime table into the visual. Drag Clocked In and Transaction columns from other two tables into the visual. If they are not exsting columns, you could create measures for them. 

If you could provide some sample data, we can help give a more specific suggestion. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Greg_Deckler]

@rlussky Well, I would get rid of the direct relationship between your two fact tables. Other than that, I'm not sure what you are trying to accomplish exactly. Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.