Connection between tables - Resolve blank cases

[SpiroswayGR]

Dear all,

I would like to create a matrix report as below : Room Typer per Row and Date for each column and the value is the capacity for each room type per day (total available rooms per room type and per day).

I have below data information:

1. Calendar with all type of dates
2. Custom Table that created with room capacities
3. Reservation tables that have complete information per day.

My goal is to create a matrix report that when one room is booked then the availability per date/roomtype will be decreased. I found a solution by connecting my custom table and reservation table by room type but i notice some blank spaces when in one random date we have zero sales per room type / per date. On this specific date , i want the max capacity/availability of this room type and not blank.

http://prntscr.com/sggiap

Any idea how to solve this or any other idea ?

Thanks in advance

Spyros

[v-lionel-msft · Accepted solution]

Hi @SpiroswayGR ,

Maybe you can create a measure similar to this.

Measure = 
VAR x = SUM([capacity])
RETURN
IF(
    x = BLANK(),
    CALCULATE( MAX( [capacity] ), ALL(Table) ),
    x
)

Best regards,
Lionel Chen

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

[AllisonKennedy]

Can  you please post the formula for the measure you are using? This shouldn't happen if you are using a MEASURE and date table, just need to get the calculation for the measure correct. 

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[SpiroswayGR]

@AllisonKennedy 

Thanks for your time.

I found 2 ways to approach the problem , 1st one relate capacity table with reservations by room type as key. But with that solution in case that i don't have any "reservation" for this specific date / specific room type then matrix is empty.

As for 2nd solution with calendar table, i am using a simple sum. i am getting correct capacity cause i am using roomtype that i have in capacity table but incorrect "measure" from reservation table.

http://prntscr.com/sghtv6

The number that show from reservation date is the "TOTAL RoomNights" per day instead of "Total RoomNights per day/per roomtype". If i will use "Roomtype" from reservation table then "RoomNights" will be corrected and "capacity column" will be incorrect.

Any idea to correct this ?

[v-lionel-msft]

Hi @SpiroswayGR ,

Has your problem been solved?

Best regards,
Lionel Chen

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