Hi! I am having trouble and I am not sure if it has to do with DAX or data modelling.
I have 4 tables as shown below:
- Date (created by me to show all dates between 1.1.2021 to 31.12.2023) Will move dynamically based on todays date.
- Reservations (containing all reservations made for housingunits or types. Some reservations will be on a specific housing unit while others will be on a specific type only)
- housingtype (containing different housingtypes and other charecteristics)
- housingunit (table with all available units)
Connections goes as follows:
Date(One) ---<>--- (many) Reservation[reservedtodate]
Reservation(Many) ---<>--- (One) Housingtype
The client is requesting to have a datepicker including all possible dates, whilst reservedtodate is only 11 rows or so with different dates inside this dateperiod. My solution was therefore to create said datetable so they would have the option to pick all the dates in the slicer.
The value I need to show them is the count av available housing units before a selected date (reservedtodate). My date slicer is therefore set as "before" the selected date. The housing units is collected from table called housingunits and counted from a column called housingunitsid where all rows are unique. They are then grouped by housingtype. See measure below.
The issue is however that some reservation are reserved only per housingtype and not housingunit while some has information in both columns. The client wants first of all a count of how many available units there are for each housing type when they select a specific date and the rule for this is they are available if reservedtodate is before selected date (hence before in date slicer).
The issues I am experiencing is that when I filter on date it will remove any blank entry of reservedtodate.
My count measure:
Count of housingunits = COUNT(housingunit[housingunitid])
I am fairly new to Power BI so I am not able to diagnose if the problem is with the modeling or with my measure.
Below is sample data for my four tables.
What my output should be is a matrix with housingtype and then housingunit in rows and count of available units as values.
Since some reservations only have housingtype and not housingunitid the rowlevel of housingtype must reflect how many avaiable pr. housingtype and the rowlevel with unit must show only the available untis.
Additional info; my tables are DirectQuery and needs to be kept that way.
Any help is highly appreciated. Please let me know if I need to share additional information!