I have a detail table in my model with a row for every hour in a day and the sales amount generated to that point of the date. Its not displayed on my report, rather it acts as the base for another reference table in the model:
Datetime Sales 2019-11-10 06:00:00 100.00 2019-11-10 12:00:00 200.00 2019-11-10 18:00:00 500.00 2019-11-10 23:59:59 999.00
The first (reference) table in the model only displays in my report the last hour value of each day in my base table. they are related via the datetime field using a 1:M with a single cross filter setting:
let Source = Base, #"Filtered Rows" = Table.SelectRows(Source, each ([Datetime] = #time(23, 59, 59))) in #"Filtered Rows"
Datetime Sales 2019-11-10 23:59:59 999.00 2019-11-11 23:59:59 950.00 2019-11-12 23:59:59 900.00
I would like to place a third table on my report that shows each of the hourly rows from the base table when a user clicks on a row in the daily table with the default setting being today's data which is added hourly.
I went down the path of using Power Query to try and filter the base table but have not been able to make it work. Should this instead be done using DAX? Either way, what would the query look like? thanks in advance!
