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 SageMy 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
Helper IISo 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?
- daxer-almighty
To answer your question.... If Hotels and Targets are 2 different business entities, then they belong to different dimensions or separate dimensions they create. If this is not the case and Targets are attributes of Hotels, then you've got 2 choices: 1) if a hotel can have 1 target, then put the target as a column in Hotels, 2) if a hotel can have 2+ targets, then you'll need a bridge table or you'll have to incorporate targets into the fact table, depending on whether the targets are "static" or "dynamic." I don't know much about your business case, so it's hard for me to give you advice. Hotels, as far as I'm concerned, should be a dimension on its own and each row should contain a unique hotel. Targets (what's that?) should most likely be a dimension on its own (but I'm not 100% sure due to lack of domain knowledge) and they should be combined in the main fact table. But it's just my feeling without a good understanding of the business constraints you're working within.
- justivan
Hi daxer-almighty ,
Thank you for all the inputs thus far. To give you a bit of an idea, I guess targets are attributes of hotels. We have some hotel partners that we have an agreement that if we reach a specific number of room nights sold, we will get a commission. The target can be more than one depending on the contract. So for example, in Hotel A. We have an agreement that if we reach 500 room nights sold, then we'll get 3% commission, if we reach 750 room nights then we'll get 5% commission instead of 3% and so on depending on the contract. So with that in mind, I created a separate table for the targets and assigned an 'ID' to each hotel then relate it to the hotels initially. So in hotels i have a PPA_ID which is foreign but there will be empty rows because not all hotels have a target. Then here goes the BLANK rows on my matrix. I decided to move the PPA_ID in the fact table itself and relate it to the target table but I got the same result as there would be reservations under a hotel that doesn't have a target so the PPA_ID will be empty as well. As for the video, I watched it. I actually watched it before but I'm not at a level yet that I could fully digest the information ALberto was saying but I'm subscribed to the channel and have been watching their videos. Also, the string column. I will surely keep that in mind and implement it as a rule of thumb.
- daxer-almighty
One more thing... remember that what you display in the UI does not necessarily have to be the value you're working with in DAX. So, if you, say, have a column with numbers (%ages) and some numbers should be BLANK, this is not the column you should expose in the UI. In this case you should create another column of string type which will hold something in place of BLANK, for instance, "N/A" or "Not Applicable." This will you expose but your calculations will be done with the numeric column. This is how it's properly done.