Forum Discussion

lcharlebois's avatar
lcharlebois
Frequent Visitor
5 years ago

Column or Measure for NOT IN List

I have 2 tables that are used primarily for bookings/accommodations, with the following fields (among others, but these are the ones applicable to my question):

  • room: room_id, room_code 
  • room_booking: room_id, check_in, check_out

I *think* I need to created a new column or measure on one of these tables in order to show what rooms are available, based on a check_in date provided.  So basically, if there's no record in room_booking for a room_id that exists in room on a specified check_in date, show the room_code.

Need Help

3 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion
    5 years ago

    lcharlebois 

    It would be great if you could provide a sample (or mockup) of your data.

    However, it sounds like you need something along the lines of:

    Measure = 
    VAR RiD = VALUES (room[room_id])
    VAR Booked = CALCULATETABLE(VALUES (room_booking [ room_id]), FILTER(room_booking. NOT(ISBLANK(room_booking[check_in]))))
    RETURN
    COUNTROWS(
    EXCEPT(RiD, Booked))

     

    Create a table visual with room [room_id] and you can add this measure to the filters for this visual in the filter pane and set the value to 1

    • lcharlebois's avatar
      lcharlebois
      Frequent Visitor
      5 years ago

      Thanks PaulDBrown!

       

      The measure you provided definitely answered my question.  Now, I might not have provided enough detail as you mentioned.

       

      This is the room table with data:

       

      This is the room_booking table with data:

       

      Next, from the report I'd like to show what rooms are booked and what rooms are available on a given date.  I'm thinking I need something similar to this:

       

       

      From the above screenshot it seems to be working as I've described for my requirements, but I think the slicer is causing me problems.  It's using the check_out column fromt the room_booking table.  And the results in both tables (left table is booked, right table is available using the measure as a filter) are not consistent when I change the date in the slicer.

       

      Any suggestions on how I can achieve this?

       

      Many thanks!

       

      • v-lionel-msft's avatar
        v-lionel-msft
        Icon for Community Support rankCommunity Support
        5 years ago

        Hi lcharlebois ,

         

        Please provide some reproducible data in tabular form, and I will provide you with a .pbix file.

         

        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.