Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
thenonexpert
Regular Visitor

Dateslicer not counting correctly

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: 

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.

Housingtype:
housingtypeidhousingtypecode

ishc

1KOD

True

2LOV

False

3DOR

False

4SOM

True

5FOL

True


Housingunit:
housingunitidhousingtypeid
4x1
3sx1
52o3
142d4
3x4

Reservation:
reservationidhousingunitidhousingtypeidreservedtodate
1234567893x41/1/2022
213789454142d48/8/2022
438493573null15/11/2022
483927483null36/11/2022

Date:
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! 


1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

2 REPLIES 2
thenonexpert
Regular Visitor

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. 

v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.