Forum Discussion

justivan's avatar
justivan
Icon for Helper II rankHelper II
5 years ago
Solved

Getting BLANK on my measure

Hello, Can someone please tell me why I'm getting empty row on my matrix? PPA Room Nights is already filtered but when I add Tier 1 % im getting an emptry row on my matrix. I've been trying to figur...
Need Help
  • daxer-almighty's avatar
    daxer-almighty
    5 years ago

    "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 🙂

     

aj1973's avatar
aj1973
Icon for Community Champion rankCommunity Champion
5 years ago

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 ResservationList
StayDays = 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 tables

I 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.

 

justivan's avatar
justivan
Icon for Helper II rankHelper II
5 years ago

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.