Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Housingtype(one)---<>---(Many)Housingunits
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:
housingtypeid | housingtypecode | ishc |
1 | KOD | True |
2 | LOV | False |
3 | DOR | False |
4 | SOM | True |
5 | FOL | True |
housingunitid | housingtypeid |
4x | 1 |
3sx | 1 |
52o | 3 |
142d | 4 |
3x | 4 |
reservationid | housingunitid | housingtypeid | reservedtodate |
123456789 | 3x | 4 | 1/1/2022 |
213789454 | 142d | 4 | 8/8/2022 |
438493573 | null | 1 | 5/11/2022 |
483927483 | null | 3 | 6/11/2022 |
1/1/2022 |
2/1/2022 |
3/1/2022 |
4/1/2022 |
5/1/2022 |
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!
Solved! Go to Solution.
Hi @thenonexpert,
When you build the relationship between power bi data model tables, these not-match records will be mapped at the 'blank' options. I'd like to suggest you create a complete calendar table and map other table fields with 'star schema'.
Understand star schema and the importance for Power BI - Power BI | Microsoft Docs
In addition, since you are working with 'direct query' mode that has limited the Dax function usage for creating calculate column/table, you may need to create this table at your datasource side.
Regards,
Xiaoxin Sheng
Hi and thanks for your response! I managed to solve this with a little DAX altering in my measures and some small modifications in my model. I had unfortunately not the chanse to create the column ay my datasource side but the workaround is working perfectly. Thanks for helping out.
Hi @thenonexpert,
When you build the relationship between power bi data model tables, these not-match records will be mapped at the 'blank' options. I'd like to suggest you create a complete calendar table and map other table fields with 'star schema'.
Understand star schema and the importance for Power BI - Power BI | Microsoft Docs
In addition, since you are working with 'direct query' mode that has limited the Dax function usage for creating calculate column/table, you may need to create this table at your datasource side.
Regards,
Xiaoxin Sheng
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
92 | |
87 | |
84 | |
65 | |
49 |
User | Count |
---|---|
140 | |
114 | |
110 | |
59 | |
59 |