Forum Discussion
Column or Measure for NOT IN List
Community ChampionIt 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
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-msftCommunity Support
Hi lcharlebois ,
Please provide some reproducible data in tabular form, and I will provide you with a .pbix file.
Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.