Forum Discussion
DAX - Variable Measure between two Fact Tables
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.
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
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!
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.
Thank you again for the response. This was exactly what I needed (adding the intervals to my dim date table).