Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi there
I have the following two tables, with an intermediary table that I created to get a relationship between them:
Table 1:
Name | EventDate | TimeInSeconds |
Doe, John | 2017/04/17 00:00:00 | 43200 |
Table 2:
ResourceID | ProjectDate | Hours |
JDoe | 2021/05/12 00:00:00 | 8 |
Table 3 (intermediary):
Name | ResourceID |
Doe, John | JDoe |
I've got a One-To-Many relationship between Table 2 and 3 (Matched on resource ID, one entry in Table 3, many in Table 2), and I've got a Many-To-Many relationship between Table 1 and Table 3 on Name.
I want to compare the TimeInSeconds column for a specific date to the Hours column for the same date (EventDate and ProjectDate respectively). When I just do the Name + ResourceID relationships and I try put everything in a table visual, EventDate and ProjectDate don't match up. When I create a relationship between EventDate and ProjectDate, EventDate stops reflecting as a date (though it's data type is datetime), and they still don't match.
How can I get this data to a place where I can use both TimeInSeconds (Table 1) and Hours (Table 2) in the same Dax formula?
Solved! Go to Solution.
Hi @cverster ,
I created a sample pbix file (see attachment) for you, please check whether that is what you want.
1. Create a date dimension table
2. Create relationships among date table(Date field), Table 1(Event Date field) and Table 2(Project Date field)
3. Create a measure to get the time of Events in hours, it will be convenient for comparison with project hours
EventHours = DIVIDE ( SUM ( 'Table 1'[TimeInSeconds] ), 3600, 0 )
Best Regards
Hi @cverster ,
I created a sample pbix file (see attachment) for you, please check whether that is what you want.
1. Create a date dimension table
2. Create relationships among date table(Date field), Table 1(Event Date field) and Table 2(Project Date field)
3. Create a measure to get the time of Events in hours, it will be convenient for comparison with project hours
EventHours = DIVIDE ( SUM ( 'Table 1'[TimeInSeconds] ), 3600, 0 )
Best Regards
Thanks for this, the intermediate date table is what I needed!
User | Count |
---|---|
98 | |
90 | |
77 | |
71 | |
64 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |