Forum Discussion
Helper IIGetting BLANK on my measure
"So in hotels i have a PPA_ID which is foreign but there will be empty rows because not all hotels have a target."
This is a candidate for a Dummy Target member in the Targets dimension. If you want a name for this target, you can call it "No Target" or sth similar. So, the rule of thumb is this. If you have a dimension with meaningful members and there are rows in your fact table where no meaningful member can be referenced, create a dummy member that will signalize the situation of not having a valid member and reference it.
All I'm telling you is to never have a model with RI problems. If you allow this, you'll have a lot of hairs pulled out of your head. I promise you 🙂
Solution Sage
Exactly as I thought... 🙂
Quick observation in DAX Studio reveals that you've got Referential Integrity violations in the model. That means your dimensions (e.g., the date dimensions and, more crucially, Hotels) get an additional virtual row that consists of BLANKs only. Please get rid of these violations. Dimensions should never, ever have a member that is totally BLANK (the virtual row). EVER. Period. To see it's really the case execute this DAX in DAX Studio connected to your model:
EVALUATE
filter(
VALUES( ArrivalDate[Date] ),
ArrivalDate[Date] == blank()
)
EVALUATE
filter(
VALUES( BookingDate[Date] ),
BookingDate[Date] == blank()
)
EVALUATE
filter(
VALUES( Hotels[HotelID] ),
Hotels[HotelID] == blank()
)
Apart from this I'd like to give you a pat on the back 🙂 The model looks OK. It's healthy schema-wise.
Community ChampionHi justivan
Indeed and totally agree with daxer-almighty .
At first glance into your model I found out that ArrivalDate and BookingDate tables are coming from the Dataflow(source)
In my previous reply I told you that you need a Calendar date table in order to use DATESBETWEEN. Also your 2 "dates" Table are linked inactively to your Reservation list.
In my opinion you should rethink about your model before going any further with your report to avoid debuggin down the road. However if you want to keep it as is then filter out the Blank row from the filter pane
Good luck
- justivan
Hi aj1973 ,
Indeed it is linked inactively because if it's active, I'm not getting the correct numbers on my measure ( as mentioned in this relative post https://community.powerbi.com/t5/Desktop/Incorrect-measure-because-of-the-context/m-p/1947755#M740570). I am now thinking to create a separate table instead to calculate the room nights and relate the 'NewTable'[Date] to 'ArrivalDate'[Date] but I'm not really sure if it's the most efficient way to approach it.
