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!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
25 | |
18 | |
15 | |
9 | |
8 |
User | Count |
---|---|
37 | |
32 | |
16 | |
16 | |
12 |