Date Filters do not work when + 0 is added

[wimsangers]

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]).

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. 

 

 I have the following relationships

Can anyone help me with this issue?

Kind regards,

Wim

[wimsangers]

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 · Accepted solution]

@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))

[wimsangers]

@amitchandak Thank you so much. It works!