March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hi Power BI Community!
I am working in a case for counting available housingunits for a studentvillage and I could really use your help.
What I would like to create is a measure counting the available housingunits categorized by housingtype for a specific date selected in a date slicer.
We have 3 tables: reservations, housingunits & housingtype as shown in the diagram below. Reservations are a DireactQuery table whilst the others are in import mode.
So what I initially have done is to create a measure counting total housingunits and categorize them by housingtype.
To calculate which ones are reserved we use the reservations table. A reservation is always created on a housingtype, but not always on a specific housingunit, which is why we can only get the count on housingtype level. For a housingunit to be available it needs to be available from the selected date in the date slicer +365 days.
Snippets of the tables with data follow below:
Housingunits
Name | housingtypeid | Housing Type | housingunitid |
JPE004H0301 | 35BA377E-021E-ED11-B83D-000D3ABB716D | Umøblert hybel | 05D49DD1-ED22-ED11-B83D-000D3A263474 |
HAS009H0203 | 6010FF2F-9395-ED11-AAD0-000D3A272DB0 | Umøblert hybel med eget bad | C65A2855-9395-ED11-AAD0-000D3A272DB0 |
eirik test enhet | 497455FB-9B89-EC11-93B0-6045BD8C3DA3 | Umøblert 1-roms tilrettelagt for rullestol | 6E6BDB81-95A3-ED11-AAD1-000D3A272DB0 |
SAN099H0505 | 75E1C2B9-EBA8-EC11-983F-6045BD88C8D0 | Umøblert 1-roms leilighet for single | 714A3392-F0D1-EC11-A7B5-000D3A496518 |
RES009H0106 | 8212737D-637F-EC11-8D21-000D3A4C3362 | ||
JBG042H0102 | 186E09A4-7395-EC11-B400-000D3AAFA383 | Møblert hybel med eget bad | 7B70A354-7595-EC11-B400-000D3AAFA383 |
JBG042H0103 | 186E09A4-7395-EC11-B400-000D3AAFA383 | Møblert hybel med eget bad | 93C6C229-8695-EC11-B400-000D3AAFA383 |
JBG042H0104 | 186E09A4-7395-EC11-B400-000D3AAFA383 | Møblert hybel med eget bad | 4707F2A8-8695-EC11-B400-000D3AAFA383 |
SAN099H0105 | 2B43C76E-EEA8-EC11-983F-6045BD88C8D0 | Umøblert 2-roms leilighet | 107D76B8-741D-ED11-B83D-000D3ABB716D |
JPE004H0203 | 35BA377E-021E-ED11-B83D-000D3ABB716D | Umøblert hybel | BBAE1140-221E-ED11-B83D-000D3ABB716D |
MAR024H0102 | 4BCC42B2-79D7-EC11-A7B6-000D3ABC4D99 | Møblert 1-roms leilighet for single | 553272EE-79D7-EC11-A7B6-000D3ABC4D99 |
MAR024H0103 | 4BCC42B2-79D7-EC11-A7B6-000D3ABC4D99 | Møblert 1-roms leilighet for single | 42618800-7AD7-EC11-A7B6-000D3ABC4D99 |
Housingtype
housingtypeid | Number of Housing Units | ishc | Main Application Profile | housingtypecode |
7795FBB6-CC22-ED11-B83D-000D3A24AADB | 1 | False | Couple | ARAF2U |
42429295-CD22-ED11-B83D-000D3A24AADB | 1 | False | Single | ARAE1U |
DD18BD9D-9195-ED11-AAD0-000D3A272DB0 | 0 | False | Single | KRIE1US |
6010FF2F-9395-ED11-AAD0-000D3A272DB0 | 0 | False | Single | CARHU |
6FADBD1E-338F-EC11-B400-000D3A2ADD32 | 10 | False | Single | SOGHM |
11B49B3D-DC90-EC11-B400-000D3A2ADD32 | 0 | False | Single | KRIHU |
D158AF0E-DD90-EC11-B400-000D3A2ADD32 | 3 | False | Single | KRIHMBE |
Reservations
reservationid | housingtypeid | housingunitid | reservedfromdate | reservedtodate | reservationtypename |
AF10BEE5-9923-ED11-B83D-000D3A24AADB | 6CA97E80-8F89-EC11-93B0-6045BD8C321C | null | 8/24/2022 12:00:00 AM | 1/1/2024 12:00:00 AM | Housing Offer |
FF4D74BA-6524-ED11-B83D-000D3A24AADB | EF7BDCE7-F59A-EC11-B400-000D3ABD82FA | null | 8/10/2022 12:00:00 AM | 6/26/2023 12:00:00 AM | Housing Offer |
1C721C31-6924-ED11-B83D-000D3A24AADB | 02E76E37-DD90-EC11-B400-000D3A2ADD32 | null | 8/10/2022 12:00:00 AM | 11/26/2023 12:00:00 AM | Housing Offer |
102634EC-1925-ED11-B83D-000D3A24AADB | 02E76E37-DD90-EC11-B400-000D3A2ADD32 | null | 8/11/2022 12:00:00 AM | 6/30/2023 12:00:00 AM | Housing Offer |
D7043ACA-CC22-ED11-B83D-000D3A263474 | 6CA97E80-8F89-EC11-93B0-6045BD8C321C | null | 8/23/2022 12:00:00 AM | 2/24/2023 12:00:00 AM | Residence |
7F744278-BB23-ED11-B83D-000D3A263474 | 35BA377E-021E-ED11-B83D-000D3ABB716D | null | 8/24/2022 12:00:00 AM | 8/31/2022 12:00:00 AM | Housing Offer |
DE19D46A-5F8B-ED11-81AC-000D3A272DB0 | 6FADBD1E-338F-EC11-B400-000D3A2ADD32 | 004FEB8F-338F-EC11-B400-000D3A2ADD32 | 1/3/2023 12:00:00 AM | 9/30/2023 12:00:00 AM | Tenancy Contract |
B0503E40-2AB1-ED11-83FF-000D3A272DB0 | 42429295-CD22-ED11-B83D-000D3A24AADB | null | 1/27/2023 12:00:00 AM | 6/30/2023 12:00:00 AM | Residence |
Any suggestions on how to resolve this is highly appreciated. And if more information is needed, please reach out.
Best.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
27 | |
17 | |
16 | |
12 | |
11 |
User | Count |
---|---|
38 | |
29 | |
24 | |
20 | |
16 |