LazyDAX v3

LazyDAX v3

Yes! I changed my LazyDAX file! my motivation: Less is more!

But what is LazyDAX?

A simple data model in Power BI and a list of DAX measures.

Simple and clear data model
small data presentation
Quick explanation and examples

LazyDAX doesn’t include long and detailed explanation or comments. For each measures and function you have a link to microsoft.com, sqlbi.com or other external website.

I would like to teach you DAX from A to Z, but it’s not the best way to do it. (At least because I’m not one of the 2 italians! I would love to have their accent!! But I’m not!!). At least for me, I learned by doing mistake and try again and again.

IMO, to learn DAX, we need to practice! And that’s what I provide you, a place where you can add / modify your DAX measures.

You can still follow links and read the Microsoft documentation! That’s the reason why I changed my presentations to keep the same Canvas as Microsoft functions lists.

Microsoft Documentation
LazyDAX function list

Why?

Now, I use DAX everyday, but my journey was long and complex! How is it possible? Before my lovely job in BI, I was a “pure” developer. (C#, Java, PHP, SQL, VB, VBA, Perl, Pascal, C, Cobol, Fortran, Ruby, … I worked and learned a ton of different languages)

BUT DAX took me a lot of time to understand! I still learn new functions or logics. I hated and loved it a lot!

Now it’s the big love and I’m trying by many methods to explain it to different people. (It’s another level of complexity)

With this updated version of LazyDAX, I wanted to provide a simple and more clear file!

This is the Power BI file where you are the hero!! DAX is your unique weapon!!

I plan to add more functions and explanation in the coming months!

You can download it now:

LazyDAX – LazySnail

How to stay always “Available” in Microsoft Teams with Excel

How to stay always “Available” in Microsoft Teams with Excel

Why?

First it was a private joke, with this article it’s now a public joke.

Our client switched from Skype to Teams and it was a very cool improvement. My colleagues told me with a bit of fun: “Teams is cool, but you can’t change the time before appearing away!”. And it’s true, we can’t! Somebody said, “If you install a software who will click randomly on your screen, you will not appear away anymore”. Yes,.. but our software policy does not allow us to install anything on our computers.

It gave me an idea! Thanks to our very good old friend, Excel! People will say “Lazy is smart”, Arnaud is “funny” or any other reason. But I had fun with Excel and his perfect VBA editor.

My colleague Isabelle (and also Maslow) will say :

https://en.wiktionary.org/wiki/if_all_you_have_is_a_hammer,_everything_looks_like_a_nail

My hammer is Excel and Code.. I agree.. (nail or snail.. it’s just a “s” who is missing)

I created a small excel sheet who will loop until you ask it to finish and will move your mouse cursor every 5 seconds.

My joy is shared between.

“I’m the Microsoft BI guy who uses Microsoft Office and VBA code to do a joke”.

and

“People will see me as a very lazy guy!! isn’t the name of his website!?!”.

Anyway, if you read this article, then it’s because I think you will pick the first idea. I’m I wrong? 🙂

Where can you download it?

You can download the excel sheet on my github repo.

(If you want your own excel sheet, the code is also available at the end of this post)

How does it look like?

This is how it looks in Excel. You can start the timer by clicking on the start button or CTRL + l (like lazysnail) or start manually the macro named “DoNotSleep”.

To stop the timer, you have to press any key.

Do you have the VBA code?

If you are curious and want to build your own Excel, let’s copy the following VBA code:

Public Declare Function SetCursorPos Lib "user32" (ByVal x As Long, ByVal y As Long) As Long
Public Declare Sub mouse_event Lib "user32" (ByVal dwFlags As Long, ByVal dx As Long, ByVal dy As Long, ByVal cButtons As Long, ByVal dwExtraInfo As Long)
Public Const MOUSEEVENTF_LEFTDOWN = &H2
Public Const MOUSEEVENTF_LEFTUP = &H4
Public Const MOUSEEVENTF_RIGHTDOWN As Long = &H8
Public Const MOUSEEVENTF_RIGHTUP As Long = &H10

Sub DoNotSleepPlease()

Dim i As Integer

For i = 1 To 9999
    'For Info, number of iteration
    'Cells(1, 1) = i
    If Cells(3, 5) = "" Then
        SetCursorPos 200, 200 'x and y position
        mouse_event MOUSEEVENTF_LEFTDOWN, 0, 0, 0, 0
        mouse_event MOUSEEVENTF_LEFTUP, 0, 0, 0, 0
        WaitPlease
        
        
        SetCursorPos 300, 300 'x and y position
        mouse_event MOUSEEVENTF_LEFTDOWN, 0, 0, 0, 0
        mouse_event MOUSEEVENTF_LEFTUP, 0, 0, 0, 0
        WaitPlease
        
        SetCursorPos 400, 400 'x and y position
        mouse_event MOUSEEVENTF_LEFTDOWN, 0, 0, 0, 0
        mouse_event MOUSEEVENTF_LEFTUP, 0, 0, 0, 0
        WaitPlease
        
        SetCursorPos 500, 500 'x and y position
        mouse_event MOUSEEVENTF_LEFTDOWN, 0, 0, 0, 0
        mouse_event MOUSEEVENTF_LEFTUP, 0, 0, 0, 0
        WaitPlease
    Else
        Exit For
    End If
    Next i
End Sub


Sub WaitPlease()
    Dim sngWaitEnd As Single
    sngWaitEnd = Timer + 5
    Do
      DoEvents
      Cells(3, 3).Value = Timer
    Loop Until Timer >= sngWaitEnd
End Sub
How I optimized my learning time with Instapaper

How I optimized my learning time with Instapaper

Introduction

I am not a huge book reader, but I read a ton of different articles on the internet. They cover multiple topics like Power BI, SQL Server, DAX, Running, World news, High Tech review, Blogging, Self-improvement, and many other topics I want to cover at a specific moment. (Now, everything related to my house, design, …)

When I browse LinkedIn or Twitter, I find interesting articles, but sometimes I do not have time to read it. (Or maybe I am not in the mood to read them right now).

I also have a big trouble (and it seems that I am not the only one on earth). On a computer or a smartphone, my focus time is quite small. I like to switch between applications and topics, which decreases completely my learning curve. You know this little monkey in your head who tries to discourage you to finish something he thinks “too serious for him”.

On this topic, I kindly recommend you to watch this TED video: https://www.youtube.com/watch?v=arj7oStGLkU

I found a solution to solve that problem, and it is a technical solution which provides me much more fun.

This solution called “Instapaper” has also a killing feature!!! Let’s see together how it works and how it can help you.

Instapaper?

First of all.

Instapaper is an application available on smartphones (IOS, Android), PC, and your internet browser.

Follow this link to access Instapaper: https://www.instapaper.com/

What can you expect with Instapaper?

Instapaper provides a clear and simple interface to collect, store and read all the web pages you like.

My workflow

On a smartphone: When I see an interesting article, I send it to my Instapaper application. This article will be synchronized across the Instapaper servers and all my different machines.

On PC: When I have a question, I go to my favorite search engine and pick up 5 or more articles and send them to Instapaper.

Later (or not), I open it on Instapaper and start to read. It’s often on my smartphone when I have time. (In the public transport, sofa, waiting time, …)

5 features I like:

  1. Reader mode: Instapaper provide a better interface for you to read the article. Better format, font size, … A dark mode is also available, which is super cool!
  2. Highlight: I don’t use it after all, but I feel more concentrated when I Highlight important text and be happy to know that I can found it back if needed.
  3. Offline mode: Ok, now we always have an internet connection. We don’t also have a roaming fee in the EU. But it’s cool to be able to read your articles and disable your internet connection and be more focused. (Hey Instapaper dev team, if you read this post: A Pomodoro timer inside Instapaper could be a huge improvement!)
  4. Archive: I can archive the articles I liked (or I read) and find it back later. No more sorry when at the coffee corner we said. “I read an interesting article, I would like to share with you, but I don’t find it back”.
  5. Export a list of your links in an HTML format (easy to share with you colleagues or friends)

And I forgot to mention: THE KILLING FEATURE!! (At least for me)

Which is for me the best thing ever and the reason why I switch to the “Pro” / “Pay” version.

A nice Kindle integration!!

Instapaper allows me to send my unread articles to my Kindle. The configuration is pretty easy, and the result is very nice!

I receive the file through my kindle email address and can read all the internet articles with the beautiful E Ink screen.

On my kindle, all the articles are grouped in “one book” and each article is separated by chapter, which allows me to switch easily between articles. All Kindle features are available, you can highlight the text you want to save, search for a translation, or a Wikipedia explanation.

Reading with a kindle, I think there is no better choice to keep the focus on this task and it gives a different atmosphere to web articles.

I wanted to share with you because I simply think it’s super nice!!!

(BTW I’m not paid to promote their tool or the Amazon Kindle 🙂 )

If you like diversity you can also use:

Pocket: https://getpocket.com/

Microsoft Edge Browser with the reading list.

In the last 6 years, I used Pocket but I quit when I saw the kindle feature on Instapaper.

Please comment if it changes your life too (or not), or if you have more tips to add!

LazyDAX V2: Learn DAX from examples

LazyDAX V2: Learn DAX from examples

Introduction

Learn from examples.

LazyDAX helps you to understand DAX with concretes and simple examples. You can change the existing measures or create yours.
LazyDAX is also an easy dataset for your tests and presentations.

Why LazyDAX?

 

In my DAX journey, I met two kinds of peoples.

Business Analyst (sometimes also Excel Users) who had very good abilities to learn DAX.

And people like me, with a slow learning curve with DAX. (Unfortunately, I’m not so bad with SQL, C#, and a ton of different languages. Yes I’m a developer :))

During our DAX learning, Mathieu Ricour and I built the first version of Lazy DAX. It was a place for us to keep and save our code around a simple and concrete example.

After many months, I’m still using this file on a quasi daily basis. And I’m convinced, it can also help you.

In this second version, I’m sharing with you a list of useful measures and a reusable dataset.

Download it on GitHub

https://github.com/arnaudgastelblum/LazyDAX/raw/master/LazyDAX.pbix

 

More measures and descriptions are coming soon.

 

 

Quick overview:

 

Power BI – Your model matters

Power BI – Your model matters

I’m trying to address a crucial topic (to me), but more complex to communicate than a technical feature.

DATA MODELING

This article gives you some tips and may give you the urge to look differently at your data. If so, my bet will be won, and you may significantly improve your Power BI trip.

Exciting, right? 🙂

Introduction

Power BI is a fabulous tool that allows us to bring together the technical world of developers and end-users.

  • For a developer, it allows us to create beautiful visualizations and highlight the data easily.
  • For business users, it simplifies the technical work to focus mainly on the result.

Having worked at several clients, I am amazed by what users initially “non-technical” can achieve!

 

Users quickly become skilled in the tool, but sometimes lack a little methodology.

A habit, unfortunately quite common, is to multiply the models and then duplicate the data.

A question, a report corresponds to a data model. This is not always the case, but it’s quite often what I saw.

Several positive points to this:

  • Speed ​​of implementation
  • The size of the model reduced to the report need (Small size).
  • It covers the need.

But also, problems:

  • Duplicate data
  • Several different models and different logics/relations. Risk of having different results from one model to another.
  • Several models, more maintenance

As a BI developer, where the situation allows, I am a staunch defender of the unique model. With other Microsoft technologies, SSIS – SQL Server – SSAS Tabular / Multidim, we are trying to create what is called a Datawarehouse.

There are several methodologies for modeling this datawarehouse, but I will not dwell on it in this article.

A Datawarehouse is a data model that makes it easy to store, access, and understand your data.

Load Before, Think After

Two worlds existed and still exist.

BI (Developers / IT Service)

Let’s talk about me since that was the case. I juggled from one project to another by managing the business part as management of apple or pear. I did not bring much interest to the job or the nature of the data. Could I reproduce the reports asked? Yes! And I think everyone was happy like that.

Complex SQL queries, performance, calculations, rules implementation were my main concerns. I was the perfect example of developer 1.0.

 

Self BI (Users / Business)

The users I met have very different technical knowledge.

  • They have no particular interests or are panicked by the sources and structure of the data at their disposal.
  • They are obviously in a hurry to provide the reports fairly quickly

For all the reasons mentioned, when creating a new report, users are eager to load the data as is.

For both “worlds” the imperatives are:

  • Load the source data.
  • Provide a table or calculation as needed.

Methodologies – Quick Win

Without going into the details of the modeling of a Datawarehouse and, more precisely, the Kimball method, I want to dwell on two tips.

  • Denormalization

  • Brainstorming

The next two topics require the first change in your habits.

Do not keep the source tables as they are in your model

When you load your data into Power BI, you can make several changes in the Query Builder.

In addition to data typing, conditions, it is essential to consolidate the data.

This is the perfect introduction for Denormalization.

Denormalization

Normalization / Denormalization ?

In computer science, we tend to standardize information.

An example will be more concrete than a definition:

A product is available with a color.

A Color entity will be created and will contain all available colors.

In the source system, a drop-down list allow the user to pick up a color.

Often the main table retains a key (foreign key) that references the other table.

This way of exploding the model into multiple tables is called “normalization”.

For our reporting needs, we have to backtrack and consolidate information into fewer tables.

If we denormalize, our example could looks like this simple table.

  • Simple for your users
  • Simple for your model
  • Simple for your DAX measures

 

Concrete example

In this example, Bill tooks all the tables and files from his source system. He decided to load the table as is, without any changes.

It was easy for him and he knows very well his model. But his users came back to him with a ton of question regarding interactions. Which tables will be impacted if they filter one specific attribute. They were not really sure.

Since they have the “Sales Person” in their model, they wanted to know if Gender or Marital Status were link to “Sales Person” or “Client”. (So bill, renamed it to make it clear)

One day Bill wakes up with a giant smile! Is it because his favorite TV show is scheduled today? Not only! He has an idea… And a straightforward one.
He decided to group his data into fewer tables. (When there is no many to many relationships).
His work life changes to better!

  • Less complex DAX expression
  • More understandable datasets
  • Less support and more time to watch his TV show. (Yes, both are compatible!)

Brainstorming

The title should perhaps have been: Do not keep the focus on the technical problems but think of yourself.

We all tend to want technical challenges, it’s addictive, and it gives us the impression of moving forward! In our Professional world, Power BI is the equivalent of Candy Crush! But I have more pleasure in aligning beautiful DAX measures in a table than sweets, do not you? For this point, I invite you to step back and step against our technical world.

 

Ask yourself the following questions: (or ask them to your users)

My advice: Take a pen, paper, coffee, soft music. Disconnect from your computers, technical “worries”. Make sure your explanations can be understood by your wife, your husband, your friends, your sports coach, your children … and yourself …

  • What is the nature of your work? Describe it to me.
  • What are you doing within the company?
  • What indicators and reports do you consult?

Take note of the information in the form of keywords and mention the frequency. Some words will stand out and can be likened to what we will call later “Dimensions”.

    Your notes could look like this word cloud.

    See if some words do not relate to each other through an idea or logic.

    For example, [Customer Code] and [Customer Name] could be grouped under the same “Customer” dimension. (While these are potentially in two different tables or source files)

    The watchword here is: DO NOT THINK TO THE TECHNICAL ASPECTS. Without knowing it, you imagine the data model dreamed for you and your users. It fits your needs, your business, and does not care (yet) about the complexity needed for the data transformation!

    In this model, an end-user will find it much more comfortable and can even build their reports. Attributes will be displayed, grouped by logical ideas, and will facilitate reporting.

    The attractiveness of Power BI and BI Self-Service, in general, will not only be available to you, but it will also be accessible to end-users.

    Now that you’ve sketched out your dimensions, you can link them together.

    You certainly have information that expresses an event with measurable data. (Additive data)

    For example:

    • A sale with a price, a quantity.
    • An inscription with a volume, a frequency.

    The relationships mentioned above correspond to Facts.

    A fact table is defined by its grain, which is itself determined by the list of dimensions attached to it.

    The sale is made:

    • By a customer
    • For a product
    • In a store
    • On a given date
    • With a means of payment

    The inscription bears:

    • On a magazine
    • By a subscriber
    • For delivery to a specific address
    • With a subscription end date

    A brainstorming session can quickly give you a good overview of your next dataset.

    Your fact table is in the middle and dimensions around.

    Where is the technical complexity for your users? For your DAX measures? Your users can now keep the focus on their results and to the more complicated question: Do I really avoid to use a Pie Chart 😉

    You can now look in detail how you will load your dimensions. Dimension by dimension asks you the question: Which is the grain of my dimension? Household, client, client history?

    And now start your candy crush session, load your data, and play a lot in the query Builder / SQL / … more …
    And do not forget to enjoy it, our work is exciting!

    How to put all this into music

    Power BI gives you the ability to consolidate your data into the Query Builder. (Power Query for the intimate)

    Do not minimize the time spent in this step, it will save you a lot later.

    You will be able:

    • Group your data (multiple source tables can be grouped) into a single table.
    • Clean up your data. (Filter unnecessary data, edit poorly formatted data) This step will provide more convenience to your users.
    • Add a type to your data.

    The interface is quite complete and allows you to perform all the desired fantasies. For performance reasons or more flexibility, you can achieve your work on data with SQL queries.

    I do not have doubts about your technical abilities to perform this task.

    The final word

    Take a step back on your technical problems, and nothing stops you! Take the time to rediscover your craft with an outside look! Power BI offers you the technical means to achieve this. And with a little methodology, you’re entering the big family of Datawarehouse’s happy modelers.

    Give me your feedback or your comments.

    I would be thrilled.

    Arnaud

     

    P.S. This article is a part of my session Power BI and Data modeling – Go to the Stars!

    Read more:

    https://docs.microsoft.com/en-us/power-bi/guidance/star-schema

    https://en.wikipedia.org/wiki/Dimensional_modeling

    https://en.wikipedia.org/wiki/Data_warehouse#Dimensional_versus_normalized_approach_for_storage_of_data

    https://www.sqlbi.com/articles/the-importance-of-star-schemas-in-power-bi/

    https://radacad.com/power-bi-basics-of-modeling-star-schema-and-how-to-build-it

     

     

    Pin It on Pinterest