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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
thenonexpert
Regular Visitor

Help with measures and logic with selected date

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. 

 

datamodell (1).jpg

 

So what I initially have done is to create a measure counting total housingunits and categorize them by housingtype.

Count of all units =

CALCULATE(DISTINCTCOUNT(housingunit[Housing Unit]),

GROUPBY(housingtype,housingtype[housingtypecode]),

USERELATIONSHIP(housingtype[housingtypeid], housingunit[housingtypeid]))
 

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

NamehousingtypeidHousing Typehousingunitid
JPE004H030135BA377E-021E-ED11-B83D-000D3ABB716DUmøblert hybel05D49DD1-ED22-ED11-B83D-000D3A263474
HAS009H02036010FF2F-9395-ED11-AAD0-000D3A272DB0Umøblert hybel med eget badC65A2855-9395-ED11-AAD0-000D3A272DB0
eirik test enhet497455FB-9B89-EC11-93B0-6045BD8C3DA3Umøblert 1-roms tilrettelagt for rullestol6E6BDB81-95A3-ED11-AAD1-000D3A272DB0
SAN099H050575E1C2B9-EBA8-EC11-983F-6045BD88C8D0Umøblert 1-roms leilighet for single714A3392-F0D1-EC11-A7B5-000D3A496518
RES009H0106  8212737D-637F-EC11-8D21-000D3A4C3362
JBG042H0102186E09A4-7395-EC11-B400-000D3AAFA383Møblert hybel med eget bad7B70A354-7595-EC11-B400-000D3AAFA383
JBG042H0103186E09A4-7395-EC11-B400-000D3AAFA383Møblert hybel med eget bad93C6C229-8695-EC11-B400-000D3AAFA383
JBG042H0104186E09A4-7395-EC11-B400-000D3AAFA383Møblert hybel med eget bad4707F2A8-8695-EC11-B400-000D3AAFA383
SAN099H01052B43C76E-EEA8-EC11-983F-6045BD88C8D0Umøblert 2-roms leilighet107D76B8-741D-ED11-B83D-000D3ABB716D
JPE004H020335BA377E-021E-ED11-B83D-000D3ABB716DUmøblert hybelBBAE1140-221E-ED11-B83D-000D3ABB716D
MAR024H01024BCC42B2-79D7-EC11-A7B6-000D3ABC4D99Møblert 1-roms leilighet for single553272EE-79D7-EC11-A7B6-000D3ABC4D99
MAR024H01034BCC42B2-79D7-EC11-A7B6-000D3ABC4D99Møblert 1-roms leilighet for single42618800-7AD7-EC11-A7B6-000D3ABC4D99


Housingtype

housingtypeid Number of Housing UnitsishcMain Application Profilehousingtypecode
7795FBB6-CC22-ED11-B83D-000D3A24AADB1FalseCoupleARAF2U
42429295-CD22-ED11-B83D-000D3A24AADB1FalseSingleARAE1U
DD18BD9D-9195-ED11-AAD0-000D3A272DB00FalseSingleKRIE1US
6010FF2F-9395-ED11-AAD0-000D3A272DB00FalseSingleCARHU
6FADBD1E-338F-EC11-B400-000D3A2ADD3210FalseSingleSOGHM
11B49B3D-DC90-EC11-B400-000D3A2ADD320FalseSingleKRIHU
D158AF0E-DD90-EC11-B400-000D3A2ADD323FalseSingleKRIHMBE

 

Reservations

 reservationidhousingtypeidhousingunitidreservedfromdatereservedtodatereservationtypename
AF10BEE5-9923-ED11-B83D-000D3A24AADB6CA97E80-8F89-EC11-93B0-6045BD8C321Cnull8/24/2022 12:00:00 AM1/1/2024 12:00:00 AMHousing Offer
FF4D74BA-6524-ED11-B83D-000D3A24AADBEF7BDCE7-F59A-EC11-B400-000D3ABD82FAnull8/10/2022 12:00:00 AM6/26/2023 12:00:00 AMHousing Offer
1C721C31-6924-ED11-B83D-000D3A24AADB02E76E37-DD90-EC11-B400-000D3A2ADD32null8/10/2022 12:00:00 AM11/26/2023 12:00:00 AMHousing Offer
102634EC-1925-ED11-B83D-000D3A24AADB02E76E37-DD90-EC11-B400-000D3A2ADD32null8/11/2022 12:00:00 AM6/30/2023 12:00:00 AMHousing Offer
D7043ACA-CC22-ED11-B83D-000D3A2634746CA97E80-8F89-EC11-93B0-6045BD8C321Cnull8/23/2022 12:00:00 AM2/24/2023 12:00:00 AMResidence
7F744278-BB23-ED11-B83D-000D3A26347435BA377E-021E-ED11-B83D-000D3ABB716Dnull8/24/2022 12:00:00 AM8/31/2022 12:00:00 AMHousing Offer
DE19D46A-5F8B-ED11-81AC-000D3A272DB06FADBD1E-338F-EC11-B400-000D3A2ADD32004FEB8F-338F-EC11-B400-000D3A2ADD321/3/2023 12:00:00 AM9/30/2023 12:00:00 AMTenancy Contract
B0503E40-2AB1-ED11-83FF-000D3A272DB042429295-CD22-ED11-B83D-000D3A24AADBnull1/27/2023 12:00:00 AM6/30/2023 12:00:00 AMResidence


Any suggestions on how to resolve this is highly appreciated. And if more information is needed, please reach out.

Best.

0 REPLIES 0

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.