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.
Helper IIHi daxer-almighty ,
I saw those when I opened my model in DAX Studio but it's still foreign to me as I'm really new to this. I ran those DAX in DAX studio and got nothing. It means that there are no empty rows right? I did make sure to remove any empty rows when cleaning my data in Power Query. One thing that's been bugging me is that, I already made the same report but I have a 1 big flat table of ReservationList where I did the all the merge in power query and I didn't get any blank result when filtering between reservations with targets and without. Another thing is that, I am under the impression that 'ReservationList' is already filtered to return only the hotels with targets under my 'PPA Room Nights' measure but when I use the measure to get the percentage against T1 , I'm getting empty rows.
PS: Thank you for your kind words regarding my model. I did a lot of reading before implementing it to my data. Got me motivated. 🙂
- daxer-almighty
My formulas return 1 empty row each. This row is artificial, added by the engine, because of the RI problems. This happens when you correctly build a star schema but not all of your fact table rows have a corresponding row in the dimension(s). Just create a default record in your dimensions and use this key for the rows in the fact table(s) instead of blanks or keys not present in the dimensions. This is the way, and the only correct, way to model dimensional data. This also saves you time because it's much easier (and also pleasant to the eye) to debug things in such an environment and it's also sooooooo much easier to write correct DAX in such a model. So then, the upshot is this: never, under any circumstances, let your model have RI problems. If you do, your formulas will tend to return wrong numbers and you'll spend a lot of time hunting subtle bugs.
Watch this: https://youtu.be/XsyeImIK-7g
- justivan
So for this case, the blanks are the hotels that are not in my targets. Do you suggest that I just include my targets in the 'hotels' table instead?
I used to have a 'Hotels' table like this and just have the target fields included in the table too. But I thought I should just create a separate list of hotels with target and assign an ID to them.
- daxer-almighty
Have you watched the video?