Today, I had some time to learn! I think I’m not that bad for data modeling, T-SQL, SQL server performance, Power Query, DAX, coaching and understanding user requirements. (Yes, sometimes it’s very good to be kind to yourself 🙂 )
But putting effort into many different techs skills, I should mention it to the whole world (at least you, because you are reading my blog post).
I’m not that good when it’s time to pick the best visual to show our data!
Table and Matrix fan
Me and the evaluation context
You will maybe recognize you in my description! I’m the kind of guy who uses Table and Matrix a lot. For testing and understanding my data! Matrix is wonderful! You can look at the behavior of your measures at the different grain in one minute! List, Sub Total, Total!! Amazing!
I can’t stop my crazy pleasure to click on all that kind of visuals
When I’m proud of it, I try to build a visual useful for my users. So I transform this table into a graphic. I click the first button, then I change for the second and I can’t stop my crazy pleasure to click on all that kind of visuals!! But do I know what I’m choosing? NO! I knew, yes.. But right now, I’m just clicking to find the best one who will give me the satisfaction between 2 tasty coffees. (And yes, Power BI is also a question of feeling and pleasure. Like a very addictive game)
With the image on top, I tested all these graphics and keep a trace of them. First, to help me the next time I need to pick the right one. (And I hope, it can help you too)
Next step? I still need to learn more about visualization! And I will! And you, do you click everywhere until you have the best one? If no, how did you become data artist / story teller?
This article seems obvious, but that’s something that I do a lot, and this is the first thing I do when somebody asks me a question.
After many weeks, some clients tell me: “Now, I’m using your technique too”
But I don’t feel to own this! It’s pretty simple, but in fact, if you didn’t use it before, it can help you a lot!
So, yes, I’m duplicating my page!
A lot!!!!
A focus version:
When I have an issue or a question, the first thing I do (after crying), it’s duplicate the page.
On this page, I remove everything not useful to test
Visuals
Images
I transform all the visuals I want to test (hopefully only one) into a table.
Let’s call it the “focus version“
Test your filter context:
When I have a cleaner version of my report I can duplicate it. (again)
On this new page, I remove one by one:
Filter on slicers
Filter in the filter pane (Be careful to remove the one hidden)
Hidden Slicers (Synced slicers)
Until my values change and I can understand them.
This step helps me to trust the data and understand the measure. It also helps me to trust the behavior of each filters.
Test one particular case:
You are the best and main debugger of Power BI!
Based on the previous topic, now you are confident with your evaluation context! I would recommend you to filter on a very specific case available in your data.
For example:
One transaction ID (one row in your sales table)
One Invoice Id
Many users don’t look their data at a very low level, and trust me, that’s very important!
If you know the story and behavior of your DAX measure for a very specific case, you can predict how it will work for 2 and many more.
Split your measures and display their result
In our DAX measure, we can have multiple steps and complexity. That’s why the best practice is to use Variables (VAR), and I know that you already use it!
But do you test it? How to do it?
Duplicate your measure (yes, Duplicate duplicate everywhere) and change the RETURN section by returning the value of one of your variable.
Many month after our work some changes can impact our measure!
Relation between tables can be different
Data aren’t the same again
You change the slicer with a different attribute! (Same value, but different attribute, ..)
By testing your old good variables, you can trust and confirm how they work. (Specially if everything is still ok for your evaluation context)
Conclusion:
Most of the time, on a report, crowded with many visuals we aren’t able to find the main reason for our “problem”.
We keep focus on not useful stuff and we are to scare (or lazy) to have a look deep dive.
In two sentences:
Less is more
Duplicate your page et destroy everything
Bonus: Duplicate page and measure is one point! But don’t forget to duplicate your .PBIX file.. In case of emergency 🙂
And when me and most of my colleagues would expect a different behavior, results are the same.
Conclusion
I think I didn’t see it before because I have a strong programmer background, and it seems very weird to me. And when I have to filter to a subset of value, I only base my FILTER on technical codes that have that kind of soft rules already applied.
After a web search, I saw that our Power BI stars (Data gods in reference to this list from Christopher Wagner) wrote very nice articles about this subject.
At this end of the year, I thought it’s time for me to bring you a gift!! It’s a very important subject that I mention to my coworkers and it’s 80% of a success power bi story!
All the focus is on the tech part.
All developers (yes, you are!!), spend a huge amount of time to understand their sources, their transformation, and the magic that happens in their DAX measures! And, to be honest, that’s normal!
So why this article?
After 1 week, 2 months, or more time, we are very proud of our dataset! (Yes, I’m not talking about reports, but dataset which can be used in many reports) Our model contains a lot of data, all the information needed is there! But is it easy for an external person to use it? (a User, a colleague, your kids, ..) Sometimes yes, sometimes no.
Some complexity exists:
Too many tables
Too many relationships and the difficulty to know the interaction between tables.
The model keeps the complexity of the source systems.
But ok! We did a huge work to clean and provide this model! Everything work!! Our users can take 2 or 3 hours and try to understand the logic behind it!
I like to mention it. I love cars!! But I know nothing about it!! Do I need to understand how an engine work to drive a car? It’s a real plus, but not mandatory.
So what do I suggest?
It doesn’t take much time, and it can help you to keep an eye on your goal. (Yes!! Provide a very shiny dataset) Before every model, I recommend you to draw your model on a sheet of paper. You already know the information you have to present and how your user group this information. If not, it’s the perfect time to ask your users and write their buzzwords on paper.
Before each project, ask them to present you their job and the different tools they use. It would help you to have a clear view of their logic behind, their needs, and also what’s missing now. At this point, I only write huge keywords on paper.
When I’m back on my desk, I’m looking at this brainstorming paper, and try to group the different keywords into a logic. That’s how tables bring to life, and because I’m a Kimball huge fan let’s call them Dimensions!
So now, with these tables you have the user point of view of their data, all the magic (and fun) is to prepare the data to fit in it! But hey, with Power Query, SQL, it’s just a piece of cake! Isn’t?
In conclusion
Do not jump directly into the source’s data, keep an eye on your expectation first. Ask your users, talk to your colleagues and try to build the model of your dream. It’s possible! During your development, you may change some tables, and that’s completely normal! But at least, if you keep your original model, everybody will win in the end.
I wish you all the best in your data journey! What a fun job!!
In our models, the best way to deals with dates and DAX intelligence functions is to use a Calendar Table (Dimension). Good news, this table can be created in 2 clicks with Power Query (Transform Data) in Power BI!
Go transform data and right click on Queries to create a new Blank Query
Copy / Paste the following code
let CreateDateTable = (StartDate as date, EndDate as date, optional Culture as nullable text) as table =>
let
DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date])),
InsertQuarter = Table.AddColumn(InsertYear, "QuarterOfYear", each Date.QuarterOfYear([Date])),
InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date])),
InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date])),
InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth]),
InsertMonthName = Table.AddColumn(InsertDayInt, "MonthName", each Date.ToText([Date], "MMMM", Culture), type text),
InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MonthInCalendar", each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & " " & Number.ToText([Year])),
InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "QuarterInCalendar", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year])),
InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date])),
InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd", Culture), type text),
InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date]), type date)
in
InsertWeekEnding
in
CreateDateTable
Choose a start date and an EndDate, click InvokeThat’s it! You have a beautiful calendar table.Do not forget to mark this table as a data table
Credit: I found this code on internet many years ago and used it in the LazyDAX file. I think I changed it, but I can’t redirect and credit the one who created it. I think it can help you a lot, that’s why I’m sharing it with you 🙂