cancel
Showing results for
Search instead for
Did you mean:
Regular Visitor

## Need help on calculating measure

I am trying to create a new measure for the below scenario.

1) I want to count the number of people that are inside a building .

Formula I used

PeopleInside = CALCULATE(DISTINCTCOUNT(Table[person_id]),isblank(Table[exit_time]))
This works perfect.

2) I want to distinct count the number of people that left the building.

Formula I used

PeopleOutside = CALCULATE(DISTINCTCOUNT(Table[person_id]),Table[exit_time] <> BLANK())

Here comes the problem :

 person_id person_name enter_time exit_time 100 xyz 04/06/2018 9:46:41 AM 04/06/2018 6:08:13 PM 100 xyz 04/06/2018 6:30:11 PM

In this case, PeopleOutside should be showing 0 because the person is inside the building but my formula shows 1 for both PeopleInside and PeopleOutside.

Could someone please help. Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft

## Re: Need help on calculating measure

Hi @SR1,

We can filter out these person still inside. Try this formula, please.

```Measure =
VAR stillIn =
CALCULATETABLE ( VALUES ( Table1[person_id] ), ISBLANK ( 'Table1'[exit_time] ) )
RETURN
CALCULATE (
DISTINCTCOUNT ( Table1[person_id] ),
FILTER (
'Table1',
ISBLANK ( 'Table1'[exit_time] ) = FALSE ()
&& NOT Table1[person_id] IN stillIn
)
)
```

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Microsoft

## Re: Need help on calculating measure

Hi @SR1,

We can filter out these person still inside. Try this formula, please.

```Measure =
VAR stillIn =
CALCULATETABLE ( VALUES ( Table1[person_id] ), ISBLANK ( 'Table1'[exit_time] ) )
RETURN
CALCULATE (
DISTINCTCOUNT ( Table1[person_id] ),
FILTER (
'Table1',
ISBLANK ( 'Table1'[exit_time] ) = FALSE ()
&& NOT Table1[person_id] IN stillIn
)
)
```

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Microsoft

## Re: Need help on calculating measure

Hi @SR1,

Did it work?

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

## Helpful resources

Announcements

#### Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors