Forum Discussion
Helper IIGetting 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 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
"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 🙂
29 Replies
- daxer-almighty
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.
- aj1973
Community Champion
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#M740570). 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.
- 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
- aj1973
Hi justivan
In your PPA Room Nights measure, you are using a Time Intelligence DAX "DATESBETWEEN". This late formula needs a Calendar Table in your model to be more accurate in its calculation. For sure there must be gaps in the dates of reservations and those Gaps are what causing the blank rows.
I see 2 options for your issue:
- You need a calendar table if you still want to use DATESBETWEEN and change your formula accordingly.
- If not, use filter pane to filter out the Blank rows.
- aj1973
Tier %1, you are asking this formula to return Blank if the _Result is Blank. You need to verify the DIVIDE where is it returning Blank... by default you are asking the DIVIDE formula to return Blank if for example this measure
[PPA Room Nights]= 0or this column
PPA_List[T1]is returning 0 or Blank somewhere.
Use this formula to verify where the Blank is
Tier 1 % Bis = DIVIDE ( [PPA Room Nights], PPA_List[T1], "Here is Blank" )
- daxer-almighty
In simple words: Nobody will be able to help you as long as you don't show the data. That's the long and short of it.
- daxer-almighty
"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 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.
- v-angzheng-msft
Community Support
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
It's good you've addressed the issue of RI violations. Have you finally managed to get this right?
