topic Re: Filters-DAX in DAX Commands and Tips

Filters-DAX

apanta — Mon, 13 Jun 2022 03:04:45 GMT

Hi Everyone,
I will try to make my query short.
So, we have this table on the server which has got the details of the rooms in each facilities and has a column called 'inactive' (boolean) to let us know if the room is active or not.

To know the occupancy rate, I am dividing the total occupied numbers by the total active rooms, and to calculate total active rooms I am using the below formula:

Active rooms = COUNTAX(FILTER('Table_name', 'Table_name'[Inactive]=FALSE()),[Inactive])

It's working totally fine but my question is, as the number of active rooms changes occassionally, how do I capture the total active rooms that were active in each day?

For eg: if my total occupancy number is 100 today and active rooms are 110, then occupancy rate will be 90.9%, which is fine for today. But if I want to see the occupancy rate for yesterday, where occupied numbers were 100 but the active rooms were 108 (let's say 2 of the rooms were in renovation), here the occupancy rate should be 92.6%. But my formula is calculating the occupancy % = 100 divided by 110 instead of 100 divided by 108. It's taking the current active room numbers. How do I solve this? The active room numbers keep changing on the server and there's no date or anything to let us know how many rooms were active in a particular day.

Sorry for making it long but I'd really appreciate if anyone can help me to solve this.

Thank you!

Re: Filters-DAX

CNENFRNL — Mon, 13 Jun 2022 09:18:24 GMT

I seems you can change your verbose measure to

Active rooms = COUNTROWS( FILTER( 'Table_name', NOT 'Table_name'[Inactive] ) )

From your description, it's inferred that 'Table_name' table is already filtered by date filter somewhere. Above all, DAX is not Excel worksheet formula; any discussion without date model is nothing but giberrish.

Re: Filters-DAX

v-yalanwu-msft — Wed, 15 Jun 2022 02:58:03 GMT

Hi, apanta ;

If you are calculating the number of active rooms per day, naturally you need a date column, so can you share what your data model looks like? What columns? do you want the output to show?

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