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''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

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

     

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    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