Getting BLANK on my measure

[justivan]

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 figure it out my self but I can't seem to get my head around it.

PPA Room Nights = 
VAR _StayDates =
    GENERATE (
        FILTER ( ReservationList, [Status] <> "Can" && RELATED ( Hotels[PPA_ID] ) <> BLANK()),
        DATESBETWEEN ( ArrivalDate[Date], [InDate], [OutDate] - 1 )
    )
RETURN
    COUNTROWS (
        FILTER (
            _StayDates,
            [Date] IN VALUES ( ArrivalDate[Date] )
            && [Date] >= RELATED ( PPA_List[StartDate] )
            && [Date] <= RELATED ( PPA_List[EndDate] )
        )
    )

Tier 1 % = 
VAR _result =
    SUMX ( PPA_List,  DIVIDE ( [PPA Room Nights], PPA_List[T1] ))
RETURN
    SWITCH (
        TRUE (),
        ISBLANK ( _result ), "N/A",
        _result >= 1, 1,
        _result
    )

Here's my model.

EDIT : PBIX HERE 

[v-angzheng-msft]

Hi, @justivan 

It seems to be a long story here and I need some time to figure it out

May I ask if you have found a solution, if you do please consider sharing it to help the other members find it more quickly. Thank you.

[justivan]

Hi @v-angzheng-msft ,

Things got hectic at work and I ended up changing my model and created an ID for all hotels without targets as @daxer-almighty suggested. Got rid of the RI violation resulting to a cleaner model.

Here's what my model looks like now. 

[daxer-almighty]

@justivan 

It's good you've addressed the issue of RI violations. Have you finally managed to get this right?

[justivan]

@daxer-almighty 
Yup. I got it right after addressing the issue of RI violations. Thanks for the advice.

[daxer-almighty · Accepted solution]

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

[justivan]

That'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]

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]

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.

[daxer-almighty]

@justivan 

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.

[daxer-almighty]

@justivan 

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.

[justivan]

Hi @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]

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.

[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]

Have you watched the video?

[aj1973]

Hi @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#M74057...). 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.