A special episode of the series Bad Practices in Power BI
WARNING!! The following article contains images and Power BI bad practices that experienced Power BI users might find disturbing and inappropriate. Accordingly, reader discretion is advised.
About the Author: Ruth Pozuelo Martinez is the MD and owner of Curbal AB, a BI consultant company based in Sweden. Ruth is also a Microsoft Data Platform MVP mainly for her contributions to the Power BI community.
She publishes weekly videos on her YouTube channel where she has more than 29k followers (at the time of writing). She also contributes on her own company blog as well as on the Microsoft Power BI community.
One afternoon back in 2018, I was once more stuck in the office doing some “urgent” work.
The autumn had already arrived in Sweden and it was foggy and raining. I could hear the distant thunderstorm ⛈ get closer and closer by the minute. At this time of the year, even though it was only 6 pm, it was pitch black and I was alone at the office.
Ka-boom!!!
Suddenly something hit my head so hard that made me I fall out of my chair and I lost consciousness for a few minutes. When I woke up, it was pitch black both outside and inside the office and the only lights I could see were the exit signs by the doors.
My head hurt and I was frightened as I was the only one left in the office. I looked outside the window and the entire city (or as far as I could see) was powerless, so I decided to wait for the power to get back on and finish I Power BI report I was working on, as I needed it for my presentation the day after.
– Working on Power BI powerless, how is that going to work out? – I joked to try to calm myself. It partially worked as I found myself giggling.
I picked up my chair from the floor, sat and grab the mouse to continue working. I logged in to windows and the Power BI user interface appeared on the screen. I was ready to finish the report for tomorrow!
– Ok, I already have product sales with Power BI, but I need both the product category, product types and the customer’s information. Getting the marketing segments would be optimal…..but how do I do that??
Suddenly I felt my stomach turn upside down on me. I started to slightly sweat and felt panic…
– I don’t remember how to work on Power BI!! Can it be related to the hit on my head after the thunder strike?
Either way, I needed to get this done for the meeting on the next morning, so I pushed myself to complete the report even though I was completely clueless how to do it.
So, I started merging all the tables into Orders. Starting with Orders Details.
Then, I extracted the fields from Order Details and proceeded to merge the other tables.
As I finished merging the tables, I felt a chill on my spine. Something felt terribly wrong. Panicked, I turned around and saw the window wide open. As the cold air was pouring in, I was relieved. It must be the window. I carried on.
– Before I import the data, I need to change the ID columns to text, so they don’t get summarized:
– Should I remove that calendar? Mmmmm, I will leave it there as I don’t have time to create a month, week…columns, but I am not sure if it’s right. Anyway, let’s load the data!
– And now I need to fix the relationships between the tables.
I moved over to the modeling tab and found that Power BI already created the relationships for me.
– Great, already fixed!! I don’t have to do it myself. Awesome! It is too late, and I am running out of time!
– Last steps – Some DAX, visuals and I can go home!
I added a few calculated columns…
Sales := Orders[UnitPrice]*Orders[Quantity]
Sales with discount := Orders[Sales]*(1-Orders[Discount])
And measures…
PY iso = CALCULATE(SUM(Orders[Sales]),SAMEPERIODLASTYEAR(‘Calendar'[Date]))
YOY = DIVIDE(SUM(Orders[Sales])-[PY iso],[PY iso])
– I think this will do:
And just when I was packing my bags, the light was back in the city and in the building, so I felt it was the perfect time to go home.
And just when I was packing my bags, the light was back in the city and in the building, so I felt it was the perfect time to go home.
I did had a feeling though that something was off, but no matter how hard I tried, I could not figure out what it was. I decided to go home.
I woke up the next day with a huge headache and a bad feeling in my body. But unable to shake it off, I went to work.
Once I got to the office, I walked to my desk, opened Power BI and clicked refresh to grab the data from yesterday and prepare for today’s meeting. As the refresh started, I left my desk to grab a cup of coffee. I met some colleagues at the coffee machine and shared my weird experience. As I returned to my desk Power BI was still refreshing!
– oh no! I killed my model!!
And at that moment, so was I, stone dead like the slow model I had created.
Do you know what I did wrong?
Can you help me bring this model back to life? You can download a smaller revision of the report here to inspect my bad practices. Let me know in the comment box below if you find what I did wrong while building my model…
… And don’t worry, If you don’t find my errors, a video will be published on Thursday on Curbal’s YouTube Channel revealing the correct way to do it and why it was wrong.
Happy Halloween!!
Ruth Pozuelo Martinez
No seeing the report, but screen I can see the problem with Auto-Time, heavy use of calculated columns, bi-directional relationships, not creating measure table, not having full year calendar table. But biggest sin I see is merging the tables instead of creating proper star-schema – all of this lookup information duplicated. Yup, this change type to text probably also didn’t help with compression.
Almost!! You are still missing one ?
Well, to start, you created one huge flat table. I did that on my first model too. 🙂 and I don’t think the SQLBI guys would like the bidirectional filtering.
That is two, you have 5 to go 😉 /Ruth
Well, to start, you create a huge, flat table. I did that on my first PBI model too. And, I am sure the bidirectional relationship is going to cause some issues.
Incorrect date sorting in column chart
Nice post I like your blog