Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi
I am trying to find away to calculate the number of occupied spaces for a given day by parking area. For example, based on the arrival and departure dates how many customers have an occupied parking spot on a given day. And then to be able to drill down to view the chart/table by parking area.
Solved! Go to Solution.
@az38 What i want to be able to see is by date the occupied spaces and to be able to drill into zone as below. And to have the total displayed. Thanking you in advance
The table below shows the correct values for zone, but not for left, right. The measure used for this is:
If I use measure 8, I get the correct values for car park (left/right) but there is no value displayed for zone.
Hi @ninakarsa
Your question is very complex and, obviously, there is not enough source data to give you a full and complex answer. but i try
first, the best practice is to create Calendar table for your task.
you could do this by create a calculated table like this
CalendarTable = CALENDARAUTO()
or
CalendarTable = CALENDAR (DATE (2019, 1, 1), DATE (2019, 12, 31))
next, you can create a dimension table 'Parking Areas' which define all reported areas (one area = one row in the table).
next, you can create a table CalendarAreasTable that will be looked like
Date | Parking Area |
01.01.2019 | Zone A |
01.01.2019 | Zone B |
01.01.2019 | Zone C |
02.01.2019 | Zone A |
02.01.2019 | Zone B |
02.01.2019 | Zone C |
03.01.2019 | Zone A |
03.01.2019 | Zone B |
03.01.2019 | Zone C |
to do that create a new calculated table
CalendarAreasTable = CROSSJOIN(CalendarTable;'Parking Areas')
next you can add a simple measure to your new CalendarAreasTable:
Measure =
CALCULATE(COUNTROWS('DataTable'),
FILTER(ALL('DataTable'), 'DataTable'[Departure Date]<=SELECTEDVALUE('CalendarAreasTable'[Date]) && 'DataTable'[Departure Date]>=SELECTEDVALUE('CalendarAreasTable'[Date]) && 'DataTable'[Parking Area]=SELECTEDVALUE('CalendarAreasTable'[Parking Area]))
)
where 'DataTable' is a table with log of your parking records
So, this measure will give you quantity of customers for each day by each Area and you could drill down it easily
do not hesitate to give a kudo to useful posts and mark solutions as solution
Thanks for your quick response, so I will attached below the tables I have and what i want to do:
So Table A has the booking information, table b has the info that i want to drill by and table c is the calender
Table A: Bookings
Customer ID | adate | ddate | car park | car park area |
123 | 01/05/2019 | 05/05/2019 | Left | Zone A |
124 | 02/05/2019 | 04/05/2019 | right | Zone B |
125 | 03/05/2019 | 05/05/2019 | Left | Zone C |
126 | 04/05/2019 | 06/05/2019 | right | Zone A |
127 | 01/05/2019 | 03/05/2019 | Left | Zone B |
128 | 02/05/2019 | 04/05/2019 | Left | Zone C |
129 | 03/05/2019 | 05/05/2019 | Left | Zone A |
130 | 01/05/2019 | 03/05/2019 | Left | Zone B |
131 | 02/05/2019 | 04/05/2019 | Left | Zone C |
132 | 03/05/2019 | 05/05/2019 | Left | Zone A |
133 | 01/05/2019 | 03/05/2019 | Left | Zone B |
134 | 02/05/2019 | 04/05/2019 | Left | Zone A |
135 | 03/05/2019 | 05/05/2019 | right | Zone B |
136 | 05/05/2019 | 07/05/2019 | right | Zone C |
137 | 06/05/2019 | 08/05/2019 | right | Zone A |
138 | 04/05/2019 | 06/05/2019 | right | Zone A |
139 | 05/05/2019 | 07/05/2019 | left | Zone B |
140 | 06/05/2019 | 08/05/2019 | left | Zone C |
141 | 08/05/2019 | 10/05/2019 | left | Zone A |
142 | 09/05/2019 | 11/05/2019 | left | Zone B |
143 | 07/05/2019 | 09/05/2019 | left | Zone C |
144 | 08/05/2019 | 10/05/2019 | left | Zone A |
145 | 09/05/2019 | 11/05/2019 | left | Zone B |
146 | 11/05/2019 | 13/05/2019 | left | Zone C |
147 | 12/05/2019 | 14/05/2019 | left | Zone A |
148 | 10/05/2019 | 12/05/2019 | left | Zone B |
149 | 11/05/2019 | 13/05/2019 | left | Zone A |
150 | 12/05/2019 | 14/05/2019 | left | Zone B |
Table B: Info
car park | car park area |
Left | Zone A |
right | Zone B |
Zone C |
Table C: Calendar
Table 😧
crossjoin(calendar,Info)
Then I use the following calcuation to give me the number of occupied spaces on a given day: but this is not returning any results
hi @ninakarsa
it seems you're confused marks < and >
try
Measure =
CALCULATE(COUNTROWS(bookings),
FILTER(ALL(bookings), bookings[adate]<=SELECTEDVALUE(CalendarAreasTable[Date]) && bookings[ddate]>=SELECTEDVALUE(CalendarAreasTable[Date]) && bookings[car park area]=SELECTEDVALUE(CalendarAreasTable[car park area]))
)
do not hesitate to give a kudo to useful posts and mark solutions as solution
@az38 @this still does not work. I am new to powerbi, so I am not understanding the issue.
Thanking you in advance
for me it works. please, share your pbix-file
do not hesitate to give a kudo to useful posts and mark solutions as solution
use
and link to ptivate message if data is confidential
do not hesitate to give a kudo to useful posts and mark solutions as solution
@az38 this seems to be working when i create a 'table' visual but when i create a matrix visual the data displays as below. Also how do i calculate the totals for this? Is there a way to create a table in powerbi with the result shown below?
to display all the data in the matrix visual press "Expand" button (see below).
and try a measure for total correct caluclation
Measure =
CALCULATE(COUNTROWS(bookings);allexcept(Bookings;Bookings[car park area]);
FILTER(Bookings; bookings[adate]<=SELECTEDVALUE(CalendarAreasTable[Date]) && bookings[ddate]>=SELECTEDVALUE(CalendarAreasTable[Date]) )
)
do not hesitate to give a kudo to useful posts and mark solutions as solution
@az38 What i want to be able to see is by date the occupied spaces and to be able to drill into zone as below. And to have the total displayed. Thanking you in advance
The table below shows the correct values for zone, but not for left, right. The measure used for this is:
If I use measure 8, I get the correct values for car park (left/right) but there is no value displayed for zone.
the same measure as i told you before
Measure =
CALCULATE(COUNTROWS(bookings),allexcept(Bookings;Bookings[car park area]),
FILTER(Bookings, bookings[adate]<=SELECTEDVALUE(CalendarAreasTable[Date]) && bookings[ddate]>=SELECTEDVALUE(CalendarAreasTable[Date]) )
)
there is a pbix-file https://ufile.io/l0a2orpl
do not hesitate to give a kudo to useful posts and mark solutions as solution
@az38thank you so much for your help...this worked perfectly. I have one more question regarding joining queries in powerbi, if you can help
SELECT A.carpark, A.carparkarea,location, B.ARRDATE, B.DEPDATE, c.amount FROM table1 A
JOIN table2 B ON B.RESID=A.RSID
JOIN table3 C ON C.RESDETAILID=A.RSDETAILID
JOIN table4 D ON A.NAME=D.NAME
Hi @ninakarsa
what do you mean with this sql-statement?
there are 3 good options to make join in power bi:
1. Merge option in Power Query Editor mode
2. Create relationships (you've got a simple one-parameter joins, as i see)
3. Join functions in DAX https://docs.microsoft.com/en-us/dax/naturalinnerjoin-function-dax
and dont forget set posts with solutions as solution for future users, please 🙂
do not hesitate to give a kudo to useful posts and mark solutions as solution
@az38Hi, you have been a great help. Following, up with this, i need to calculate % occupied spaces (occupied/total spaces). The table below shows the total spaces by car park and car park area. How can I use dax to get the following result:
01/05/2019 | |||
occupied spaces | total spaces | occupancy | |
Zone A | 1 | 30 | 3.3% |
Left | 1 | 10 | 10.0% |
Right | 20 | 0.0% | |
Zone B | 3 | 45 | 6.7% |
Left | 3 | 30 | 10.0% |
Right | 15 | 0.0% | |
Zone C | |||
Left | |||
Right |
Car Park | Car Park Area | From Date | To Date | Spaces |
Zone A | Left | 01/05/2019 | 20/05/2019 | 100 |
Zone A | Right | 01/05/2019 | 20/05/2019 | 50 |
Zone B | Left | 01/05/2019 | 20/05/2019 | 320 |
Zone B | Right | 01/05/2019 | 20/05/2019 | 150 |
Zone A | Left | 01/06/2019 | 20/06/2019 | 150 |
Zone A | Right | 01/06/2019 | 20/06/2019 | 75 |
Zone B | Left | 01/06/2019 | 20/06/2019 | 340 |
Zone B | Right | 01/06/2019 | 20/06/2019 | 150 |
I do not understand how do you calculate total space and how it connects with Spaces from the second table
do not hesitate to give a kudo to useful posts and mark solutions as solution
so lets say we have a table with information regarding to the car park table A. Table B, is a calcuated table showing by date customers in the car park area, so for example on the 01/05/2019 1 customer was in car park zone A & left car park area, 4 customers were in car park zone B with 3 in left car park area and 1 in right car park area. And then want to combine the two tables to say that when Date from table b is between from date and to date in table A and car park and car park area from B is equal to car park and car park area from table a then return the number of spaces in table B. This should the return table C
And finally to create a measure which will return results in table d
Table A
Car Park | Car Park Area | From Date | To Date | Spaces |
Zone A | Left | 01/05/2019 | 20/05/2019 | 10 |
Zone A | Right | 01/05/2019 | 20/05/2019 | 20 |
Zone B | Left | 01/05/2019 | 20/05/2019 | 30 |
Zone B | Right | 01/05/2019 | 20/05/2019 | 15 |
Zone A | Left | 01/06/2019 | 20/06/2019 | 15 |
Zone A | Right | 01/06/2019 | 20/06/2019 | 60 |
Zone B | Left | 01/06/2019 | 20/06/2019 | 35 |
Zone B | Right | 01/06/2019 | 20/06/2019 | 70 |
Table B:
Date | CustomerID | CarPark | CarPark Area |
01/05/2019 | 123 | Left | ZoneA |
01/05/2019 | 127 | Left | ZoneB |
01/05/2019 | 130 | Left | ZoneB |
01/05/2019 | 133 | Left | ZoneB |
Table C:
Date | CustomerID | CarPark | CarPark Area | Spaces |
01/05/2019 | 123 | Left | ZoneA | 10 |
01/05/2019 | 127 | Left | ZoneB | 30 |
01/05/2019 | 130 | Left | ZoneB | 30 |
01/05/2019 | 133 | Left | ZoneB | 30 |
table 😧
01/05/2019 | |||
occupied spaces | total spaces | occupancy | |
Zone A | 1 | 30 | 3.3% |
Left | 1 | 10 | 10.0% |
Right | 20 | 0.0% | |
Zone B | 3 | 45 | 6.7% |
Left | 3 | 30 | 10.0% |
Right | 15 | 0.0% | |
Zone C | |||
Left | |||
Right |
maybe you just need to do smth like for calculate occupied places?
Measure Spaces =
CALCULATE(SUM('Table A'[Spaces]),allexcept(Bookings;Bookings[car park area]),
FILTER(Bookings, bookings[adate]<=SELECTEDVALUE(CalendarAreasTable[Date]) && bookings[ddate]>=SELECTEDVALUE(CalendarAreasTable[Date]) )
)
then divide
occupancy = divide([Measure], [Measure Spaces])
where measure - is a measure from above solution
do not hesitate to give a kudo to useful posts and mark solutions as solution
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
45 | |
26 | |
22 | |
13 | |
8 |
User | Count |
---|---|
73 | |
51 | |
45 | |
16 | |
12 |