Power BI Visuals List: Which Graphic to choose?

by Arnaud Gastelblum | Non classé

Click on the image to show an example for each graphics available in Power BI

You can also download the Lazy DAX file Here (Where all examples are available)

Why this list?

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

Table and Matrix fan

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)

More links

Visualization types in Power BI – Power BI | Microsoft Docs

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?

Power BI: Duplicate of Duplicate – The best way to test your reports and measures

by Arnaud Gastelblum | Non classé

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!

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:

1. Less is more
2. 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 🙂

I wish you all the best and a lot of duplication.

DAX is case insensitive

by Arnaud Gastelblum | Non classé

This week with my colleague Vincent Philipot, we learned something that many of you already know. (But maybe not!)

What does it mean?

Here is a short description available on Wikipedia

But because an example is worth a thousand words, here is some screenshot. (You can download this example in the lazydax file)

In our dataset we have 10 differents Products with a ProductCategory = Vegetable

I wrote 3 differents measures to count the number of product with a ProductCategory equal to:

Vegetable

Nb Vegetable = 
    CALCULATE(
        COUNTROWS('Product')
        ,FILTER('Product', 'Product'[ProductCategory] = "Vegetable")
    )

VEGETABLE

Nb VEGETABLE _ = 
    CALCULATE(
        COUNTROWS('Product')
        ,FILTER('Product', 'Product'[ProductCategory] = "VEGETABLE")
    )

VeGeTaBlE

Nb VeGeTaBlE __ = 
    CALCULATE(
        COUNTROWS('Product')
        ,FILTER('Product', 'Product'[ProductCategory] = "VeGeTaBlE")
    )

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.

Letter case-sensitivity in DAX, Power BI and Analysis Services – SQLBI

Chris Webb’s BI Blog: Power BI And Case Sensitivity Chris Webb’s BI Blog (crossjoin.co.uk)

You can download this example in the lazydax file here

In all the cases, I hope that you learned something new! 🙂

Your data model first!

by Arnaud Gastelblum | Non classé

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!!

Power BI Create a Calendar table automatically

by Arnaud Gastelblum | Non classé

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

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 🙂