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
Europe Fabric Conference

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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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