Forum Discussion

wimsangers's avatar
wimsangers
Icon for Helper I rankHelper I
5 years ago
Solved

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...
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    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's avatar
wimsangers
Icon for Helper I rankHelper I
5 years ago

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