cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

sql code in Dax

Hi All,

 

I am trying to calculate the spaces occupied in the car park area by day and to show whether they are arriving on date, or were already in car park and have yet to depart.

For example: on the 04/01/2019, there would be 3 customers in the car park (of which 1 arrived on the day, and 3 were already in the car park)

Customer, Date Arrival, Date Departure

123, 01/01/2019, 03/01/2019

124, 02/01/2019, 06/01/2019

125, 01/01/2019, 07/09/2019

126, 04/01/2019, 05/01/2019

 

In sql I would use the following code, is there a way to replicate this in powerbi?

select a.Date,count(a.[customer]),a.Flag, CarPark
from


(SELECT DISTINCT D.Date, F.[customer],case when F.[Booking_Arr_Date]=D.Date then 1 else 0 end as "Flag",
FROM [DimDates] D
JOIN [Car Park Data] F
ON F.[Booking_Arr_Date]<=D.Date AND (F.[Booking_Dep_Date]>=D.Date)

) a

group by a.Date,a.Flag,CarPark

6 REPLIES 6
Highlighted
Super User IV
Super User IV

Re: sql code in Dax

Hey @ninakarsa ,

 

first I created a very simple calendar table using this DAX (there are much more sophisticated DAX statements available, just search for DAX calendar):

Calendar = 
var DateStart = MIN('Table'[Date Arrival])
var DateEnd = MAX('Table'[Date Departure])
return
CALENDAR(DateStart , DateEnd) 

Please make sure that there is no relationship between the Calendar table and the containing the arrival and departure date, this table is called table in my example.

I created the measure "present" using this DAX:

present = 
var _date = CALCULATE(MAX('Calendar'[Date]))
return
CALCULATE(
    COUNTROWS('Table')
    , FILTER(
        'Table'
        , 'Table'[Date Arrival] <= _date && 'Table'[Date Departure] >= _date
    )
)

and the measure arrived using this DAX:

arrived = 
var _date = CALCULATE(MAX('Calendar'[Date]))
return
CALCULATE(
    COUNTROWS('Table')
    , FILTER(
        'Table'
        , 'Table'[Date Arrival] = _date
    )
)

This allows to create a table visual like so:

image.png

Please be aware that the date column in the table visual above is the date column from the Calendar table.

 

If you are searching for event-in-progress you will find some articles that provide more background for similar questions than my answer will be able to provide.

 

Hopefully this is what you are looking for

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Highlighted
Helper II
Helper II

Re: sql code in Dax

  

Thanks for your prompt reply, now say i want to be able to drill on car park  and car park area to get the final results how can this be done. Bookings table below

 

 

Booking Data:

 

Customer IDadateddatecar parkcar park area
12301/05/201905/05/2019LeftZone A
12701/05/201903/05/2019LeftZone B
13001/05/201903/05/2019LeftZone B
13301/05/201903/05/2019LeftZone B
12402/05/201904/05/2019rightZone B
12802/05/201904/05/2019LeftZone C
13102/05/201904/05/2019LeftZone C
13402/05/201904/05/2019LeftZone A
12503/05/201905/05/2019LeftZone C
12903/05/201905/05/2019LeftZone A
13203/05/201905/05/2019LeftZone A
13503/05/201905/05/2019rightZone B
12604/05/201906/05/2019rightZone A
13806/05/201908/05/2019rightZone A
13606/05/201908/05/2019rightZone C
13906/05/201908/05/2019leftZone B
13706/05/201908/05/2019rightZone A
14007/05/201909/05/2019leftZone C
14309/05/201911/05/2019leftZone C
14109/05/201911/05/2019leftZone A

 

 

result.PNG

@TomMartens

Highlighted
Super User IV
Super User IV

Re: sql code in Dax

Hey @ninakarsa 

 

please explain the format of the date columns:

 

MM/DD/YYYY or DD/MM/YYYY

 

and also provide the details about the final outcome that you are looking for.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Highlighted
Super User IV
Super User IV

Re: sql code in Dax

Hey @ninakarsa 

Much more important, take the time and provide date value in the sample data using the ISO format like so

YYYY-MM-DD, meaning October, 17th 2019 looks like this: 2019-10-17.

Sure this will take extra effort, but will avoid misunderstandings, consider this a kind act.

 

All other date values will lead to misunderstandings due to the regional settings of the international audience of this forum.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Highlighted
Helper II
Helper II

Re: sql code in Dax

@TomMartens  thanks for this.

 

Now lets say I have a table with information regarding to the car park table A which gives the number of spaces in the car park area during a given timeframe.

 

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, 3 customers were in car park zone B with 3 in left car park area.

 

I 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 from table A. This should return table C 

 

And finally to create a measure which will return results in table d

 

Table A:

Car ParkCar Park AreaFrom DateTo DateSpaces 
Zone ALeft01/05/201920/05/201910
Zone ARight01/05/201920/05/201920
Zone BLeft01/05/201920/05/201930
Zone BRight01/05/201920/05/201915
Zone ALeft01/06/201920/06/201915
Zone ARight01/06/201920/06/201960
Zone BLeft01/06/201920/06/201935
Zone BRight01/06/201920/06/201970

 

Table b:

DateCustomerIDCarParkCarPark Area
01/05/2019123LeftZoneA
01/05/2019127LeftZoneB
01/05/2019130LeftZoneB
01/05/2019133LeftZoneB

 

Table c

DateCustomerIDCarParkCarPark AreaSpaces
01/05/2019123LeftZoneA10
01/05/2019127LeftZoneB30
01/05/2019130LeftZoneB30
01/05/2019133LeftZoneB30

 

then measures to show

01/05/2019 occupancy for carpark left zone b was (count(customerid)/30)= 3/30

 

Highlighted
Super User IV
Super User IV

Re: sql code in Dax

Hey @ninakarsa ,

 

to avoid any confusion about the date format, upload a pbix that contains sample but still represents your data model to onedrive or dropbox and share the link, if you use xlsx file(s) to create the sample data, upload these file(s) as well.

 

Why is table B calculated, and what is the source for this calculation?

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Don't miss the Power BI Dev Camp this week!

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.