I'm struggling to figure out the best way to tackle this problem.
I have an SQL db with PBI gateway, Premium Service.
I have a table like below:
ID |Overidden ID |Owning Location |DelLocation |Start Date |EndDate |Poperty1 |Property2 | etc 1 |0 |Loc1 |Cus1 |2018-07-01 |2018-08-01 |True |12 | 2 |1 |Loc1 |Cus1 |2018-07-10 |2018-07-10 |True |10 |
I want to display 7 days data using a slicer. So if I used a slicer and selected Wc 1st July. Then I would only display the details for ID 1. But if I filtered on Wc 8th July then I need to display the detail of ID1 for everyday other than the 10th. There can also be overidden overides, say the original could last 3 months, then an overide for a week in the middle then another overide for one day within the middle week.
I have zero control over the SQL db.
Does Wc mean "Work Calendar"?
"display the detail of ID1 for every day" means repeating ID 1 for 7 times, doesn't it?
Can you post the expected result, please?
I was thinking of a Matrix basically like the pic above.
The Week commencing would be a slicer from a date table I have.
The days would need to be measures I think as the top row would need to know that it was overidden on the Tuesday in this case.
There could be a solution. What do the other records look like? Please refer to the demo in the attachment.
1. Create two date tables. One for creating a relationship.
2. Create a measure.
Measure = VAR selectedDate = IF ( SELECTEDVALUE ( Slicer[Date] ), SELECTEDVALUE ( Slicer[Date] ), CALCULATE ( MIN ( 'Calendar'[Date] ), ALL ( 'Calendar' ) ) ) VAR startDate = CALCULATE ( MIN ( Table1[Start Date] ), ALL ( 'Calendar'[Date] ) ) VAR endDate = CALCULATE ( MIN ( Table1[EndDate] ), ALL ( 'Calendar'[Date] ) ) RETURN IF ( MIN ( 'Calendar'[Date] ) >= selectedDate && MIN ( 'Calendar'[Date] ) <= selectedDate + 6, IF ( MIN ( 'Calendar'[Date] ) >= startDate && MIN ( 'Calendar'[Date] ) <= endDate, 1, BLANK () ), BLANK () )