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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
wimsangers
Helper I
Helper I

Date Filters do not work when + 0 is added

Hi all,

 

I am trying to calculate the occupancy rate per hour per location. I have managed to do so using the following formula

DISTINCTCOUNT('bi chargesession (2)'[evse_id])/DISTINCTCOUNT('EVSE''s per klant'[EvseId]).
wimsangers_2-1628605177119.png

 

However There are hours where no one is at a location. I want to display a value of 0 then.
But when I update the formula to this: 
IF(CALCULATE(DISTINCTCOUNT('bi chargesession (2)'[evse_id]),'bi chargesession (2)'[Hoofdklant] <> BLANK())/DISTINCTCOUNT('EVSE''s per klant'[EvseId]) = BLANK(),0,CALCULATE(DISTINCTCOUNT('bi chargesession (2)'[evse_id]),'bi chargesession (2)'[Hoofdklant] <> BLANK())/DISTINCTCOUNT('EVSE''s per klant'[EvseId])
My filtering on the page does not work anymore. 
 wimsangers_1-1628605124192.png

 

 I have the following relationships
 
wimsangers_0-1628604893032.png

 

Can anyone help me with this issue?

 

Kind regards,


Wim

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@wimsangers , +0 forces left join.

 

I try this kind of measures in the past

0 between range
Measure = var _1= SUM(Opportunity[Opportunity count]) +0
var _min = minx(ALLSELECTED('Calendar'), 'Calendar'[Date])
var _max = maxx(ALLSELECTED('Calendar'), 'Calendar'[Date])
return
CALCULATE(if(max('Calendar'[Date]) <_min || max('Calendar'[Date]) >_max , BLANK(), _1))

 

View solution in original post

3 REPLIES 3
wimsangers
Helper I
Helper I

Hi @amitchandak,

 

Two days ago you have helped met greatly by finding a solution to the problem mentioned above.

I have a follow up question on this, and I hope you can help me. I want to calculate how many hours there was an occupany rate of 100%.

 

I have tried it with the following measure: 

aantal uur 100% bezetting = IF([bezettingsgraad3] = 1,DISTINCTCOUNT('Dim_calendar date/time'[Date Time]),0).
And also with this one: 
IF(COUNTA('bi chargesession (2)'[HoursBetweenList]) = VALUES('bi chargesession (2)'[aantal evse subklant]) ,DISTINCTCOUNT('bi chargesession (2)'[HoursBetweenList]),0).
 
Both are only working if I select and hour, day, month, year where there was and occupancy rate of 100%. Then is gives me the value 1. Otherwise it is always 0. My goal is that by filtering on months or days I get the number of hours that there was an occupancy rate of 100%.
 
Can you help me finding the right measure?
 
Best regards,
 
Wim
amitchandak
Super User
Super User

@wimsangers , +0 forces left join.

 

I try this kind of measures in the past

0 between range
Measure = var _1= SUM(Opportunity[Opportunity count]) +0
var _min = minx(ALLSELECTED('Calendar'), 'Calendar'[Date])
var _max = maxx(ALLSELECTED('Calendar'), 'Calendar'[Date])
return
CALCULATE(if(max('Calendar'[Date]) <_min || max('Calendar'[Date]) >_max , BLANK(), _1))

 

@amitchandak Thank you so much. It works!

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.