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"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 🙂
Helper IIThat's what I was thinking as well to have an ID assigned to all rows in my fact tables that are not member of the target but I wasn't sure how I should put it in my dimension table.
- aj1973
Community Champion
Hi justivan
On my end I used different approach:
As you can see I replaced HotelName_PPA by HotelName from the Reservationslist table
The reason is simple, you have Blank rows in this late table of the column
Therefore your formula PPA Room Nights is including in its calculations the blank rows and since the tables are related at the PPA_ID
then the Blank rows have to show in the Matrix.
MY
Tier 1%__ = DIVIDE ( [PPA Room Nights], SUM(Targets[T1]), "Here is Blank" ) as you can see I used SUM inside the DIVIDE, the rest you can adjust it as you wish.One last remarque if you may, for the number of staying days I added a column to the ResservationListStayDays = DATEDIFF(ReservationList[InDate] , ReservationList[OutDate] , DAY)I see it's better to use this column inside of the reservation table than use the other 2 tablesI recommend that you keep things simple and readable and a Star schema model could avoid you much trouble
if necessary you can add a calendar table related to InDate (Active) & OutDate(Inactive) for Time intelligence calculations.
By the way the video provided by daxer-almighty tells it all, and I myself am in the business for Camp managements and for Mining Companies.
- daxer-almighty
I'd suggest you resist the possible urge to apply aj1973's solution. He says:
"As you can see I replaced HotelName_PPA by HotelName from the Reservationslist table."
The problem with this approach is that in a good, correct model no columns from a fact table should be placed in visuals. NEVER. Only columns/attributes from dimensions should be used. There are too many reasons behind this rule, so we don't have time to discuss this here. But beware of putting columns from a fact table directly on the canvas!
- justivan
Hi daxer-almighty ,
I read in an blog as well ( can't remember who ) that categories placed in a visual should always be from the dimension table.
- justivan
Hi aj1973 ,
The reason why I used the HotelName_PPA is that there are hotels under 1 chain combined with 1 target. For example PPA_ID 3 is a combination of 3 hotels. All productions of 3 hotels will be combined to achieve 1 . As for the BLANK rows, it is still the part that I'm really confused because on my measure, I filtered the table to return only the bookings where there are targets.
VAR _StayDates = GENERATE ( FILTER ( ReservationList, [Status] <> "Can" && RELATED ( Hotels[PPA_ID] ) <> BLANK()), DATESBETWEEN ( ArrivalDate[Date], [InDate], [OutDate] - 1 ) )Regarding the staying days you mentioned in your last remark, there is actually already a column in my fact table called 'Days' that returns the same number. The reason why I didn't use that is because if the booking falls on 2 different months, the actual room night sold will be based on the [InDate] only. So if I have a booking from 28 Jan - 05 Feb for example, the 8 days will be counted as room nights sold for January instead of 4 in January and 4 in February.
