DAX - Variable Measure between two Fact Tables

[JamHam]

Hello,

I am attempting to display Scheduled hours in the same table as Sales and Checks per hour. The Sales table is a direct query to a data lake enviroment, and the Scheduled hours is an excel import.

The desired result is to have the data (in red) from Table 2 be displayed in Table 1. Where the scheduled hours displayed are for that specific hour and day, and not the sum of Scheduled Hours. With the added request of being dynamic to be filterable down to a specific day of week based on the Slicer used from the Sales table. 

Below is a simplified view of the model that is currently built.

Here is the calculation I have for Friday Scheduled hours.

Friday Scheduled Hours = 
var SchedDayofWeek =
    FILTER('Scheduled Hours','Scheduled Hours'[Day Of Week]="Fri")

RETURN
    CALCULATE([Total Scheduled Hours],SchedDayofWeek
)

Below is a link to the Power BI report.

Power BI - Sales - Hours Test Workbook 

I have tried  RELATEDTABLE and USERELATIONSHIP but have been unsuccessful and my DAX knowlege is fairly limited so far.

Thank you for your time.

[hansontm]

I would recommend creating 2 new dimension tables (Date Intervals and Stores) and relating the dimension tables to Sales and Scheduled Hours separately. Also will need to remove the existing relationship between Sales and Scheduled Hours. Then you can use the dimension tables in the slicers. 

Or you could merge the two tables in power query based on a concatenation of store, date, and interval

[JamHam]

Thank you for the response. I should have added that the original Semantic Model has Dim Date and Dim Store tables. But the IntervalHourStartTM lives in the Sales Fact table.

I added a Dim Date and Dim Store tables and updated the relationships for the fact tables to reference the Dim tables. The connectionss from the Fact tables to Dim date is on Day of Week. The connections from the Fact tables to Dim store are on StoreID.

Power BI - Sales - Hour Test Workbook - Updated 

With the updated connections I now get a total rollup in the Sales and Checks measures. I have a feeling that I have the relationships set up incorrectly but not sure where.

 

I'll have to connect with our developers to see if we are able to accomplish your suggestion of merging and concatinating Store/DayofWeek/IntervalTime. As I think this might be the easiest solution to what I am trying to accomplish.

Thanks again hansontm!

[hansontm · Accepted solution]

Yes your relationships (primary key columns) will not work if your matching on day of week. Since there are multiple records in fact tables for each day of the week, it sums them up. If you modify your date table to the interval level then match on that key, the measures will calculate at the appropriate level.

[JamHam]

Thank you again for the response. This was exactly what I needed (adding the intervals to my dim date table).