Featured

Introduction

The big question: What exactly is Power BI?

Short answer: A data visualization tool.

Long answer: Though primarily it is a data visualization tool, Power BI is a very powerful self service BI tool. It is made up of multiple components and they all work in tandem to provide you with a wholesome data discovery experience.

The usual flow of data in Power BI is from fetch data from Sources (both online and on cloud) to Power BI Desktop (though the data can be fetched directly into Power BI service too), model the data, transform the data, apply logic, apply calculations and then finally publish the data to Power BI Service for others to see, collaborate or create content on top of it.

To achieve this Power BI makes use of various components such as Power Query, Power BI Desktop, Power BI Service, Gateways, Power Shell to name a few. Please visit the links to get in depth know how on individual components.

Creating Histogram in Power BI – Method 1

Hello all,
In this series of blog posts, we will look into 2 different ways to create a histogram (bins) in Power BI.

Let us first talk about the data that we will be using for this post. The sample data can be downloaded here.

Our sample data contains columns such as employee ID, sale amount, hours, and name of each employee.
The task that we need to accomplish is to bin the count employees by their efficiency. In other words, we need to find how many employees have X efficiency and how many employees have Y efficiencies so on and so forth.

We will be taking two approaches to achieve this. Method 1 will create a calculated column in the data table and we will use the SWITCH() function to bin the employees.
Method 2 will make use of the disconnected table and using the VALUES() DAX function we will find the corresponding employee.


Let us begin with method 1.

Click on get data and select the sample file.

  1. At this point, there is no need to transform the data as our data for this exercise is already clean. Once the data is loaded, click on the data/model tab and right-click on the table name and let us first rename it to Main_Data.

First of all, let us create the following measures. These are Total Sales, Total Hours, and Efficiency.

  • Total Sales is the sum of all the sale amount.
  • Total Hours is the sum of all the hours.
  • Efficiency is Total Sales divided by Total Hours. For Efficiency measure, we will use the DIVIDE() function.  
  • Right-click the table (Main_Data) and click a new measure.
  • Let us call it Total Sales. After the ‘=’ sign, type in SUM(Main_Data[Sale Amt]) and hit enter.
  • As a good practice, immediately click the $ symbol and choose the currency format. (I have USD and I have also comma as thousand separator).

Repeat the same process for Total Hours. The DAX formula for Total Hours is Total Hours = SUM(Main_Data[Hours]).

Repeat the same process for Count_Names. The DAX formula for Count_Names is Count_Names = COUNT(Main_Data[Emp Id]).

Finally, let us create an Efficiency measure by using the following function. Efficiency = DIVIDE([Total Sales], [Total Hours],0).

Click on the Report Canvas tab and select the matrix visualization from the list of Visualizations. 

In the Rows field, drag and drop the names. In the Values field, drag and drop the Efficiency.

As we can see from the visual, 11 Names are corresponding to the efficiency of 100. 

Now our next task is to bin employees whose efficiency is from 0-10, 11-20, etc. 

Let us go back to the data/model view and click on the new column under the modeling tab. 

In the formula bar input the below formula.

BinCol = 

VAR mylbshr = [Efficiency]

RETURN
            
SWITCH (
        TRUE (),
        mylbshr <= 10, "<= 10",
        mylbshr > 10
            && mylbshr <= 20, "10 - 20",
        mylbshr > 20
            && mylbshr <= 30, "20 - 30",
        mylbshr > 30
            && mylbshr <= 40, "30 - 40",
        mylbshr > 40
            && mylbshr <= 50, "40 - 50",
        mylbshr > 50
            && mylbshr <= 60, "50 - 60",
        mylbshr > 60
            && mylbshr <= 70, "60 - 70",
        mylbshr > 70
            && mylbshr <= 80, "70 - 80",
        mylbshr > 80
            && mylbshr <= 90, "80 - 90",
        mylbshr > 90
            && mylbshr <= 100, "90 - 100",
        mylbshr > 100, " More than 100"
    )

  1. Let us break down this formula. 
    • We are declaring a variable and storing the value of the measure ([Efficiency]) in it. 
    • We are then returning the value based on the SWITCH() function.
    • We are saying that if the efficiency is <=10 then return me “<=10” else continue. 
    • This function evaluates all the rows and inputs the value based on this test in the Bin_Col column. 

Click the Clustered Column Chart icon in the visualizations pane. Drag and drop Bin_Col in the Axis field and Count_Names in the Values field. 

We have now created histogram/binning in Power BI. In the next blog post, we will create a very similar histogram but using the Disconnected Tables method. Please do let me know what you think of this. 

Till next time !!!

Demystifying Date Dimension Part 4

Let us pick up where we left off. In part 3 we created a date table but in a very brute force method. Let us make this more dynamic in this series.

If you followed me every step of the way you should already have this in your table. If not then just click on the blank query and paste the following code to bring you up to speed.

let
    Source = List.Dates(#date(2020,1,1), 366, #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}})
       
in
    #"Changed Type"

Now that we are on the same page, let us begin!!!
I hope you still remember the three main requirements for a date table. I know I have said this over and over, but once again I will reiterate this that to have a date table, we need StartDate, EndDate, and Duration.

Now with this in mind, let us begin. First, we need to find the StartDate. In this case, it is 01/01/2020.

Click on the function icon (fx) and insert a new step.

By default, you should see that this new step is called Custom 1 and that it is referencing the previous step as shown below. In my case it is referencing the step called Inserted Day of Year, for you, it could be different. But the main point here is whenever we add a new step, then by default, it will always refer to the immediate prior step.

Delete everything including the ‘=’ sign and type in 01/01/2020 and while you are at it, rename this step (right-click on the Custom 1) to StartDate.

This will fetch us the StartDate. Since now we have StartDate, go ahead and replace #date(2020, 1, 1) in the source step with StartDate. 

Now we need a count of days. For this let us dynamically get the count of days from 01/01/2020 till today. 

Create a new step and input this DateTime.Date(DateTime.LocalNow()) in this formula bar and rename this step as EndDate.

This nested function first fetches the local/current date in DateTime format, then we extract just the date component from it. 

Insert another step and this time we will invoke Duration.Days function and wrap it around EndDate minus StartDate. This time we are subtracting the StartDate from EndDate and fetching the number of days from it. (If you want to include today also then we must add 1 to it so we can have today counted as well). Let us rename this new step as Count.

At this point, we have StartDate and Count. Let us navigate back to the source step and replace the hardcoded 366 with Count. 

Now insert another step and reference it back to the Changed Type step in the applied steps as shown below. By default, every new step references the immediate prior step, but we want to reference another step hence, the need to manually tell M query where to look for. 

Once we have the above step, all that is needed is to extract relevant date parts as we did in the previous blog. 

At this point, our Date Table is ready. We can use this code and just by changing the StartDate we can generate all the dates till today. I changed my StartDate to 01/01/2010 and I can now see all the dates from 01/01/2010 till today.

If you noticed, we had to manually enter the StartDate but what if we wanted to use the earliest date that is present in our model. Well, it is very easy to do so.

In our sample, we have an Orders table. Let us say that we wanted to use the first Order Date as my StartDate.
We can call another List function called List.Min and pass that to our source. Let us see that in action.

For the sake of simplicity and comparison, I am going to insert another step right after StartDate. Since we know that every new step in M Query references the immediate prior step, inserting a new step right after StartDate will not break our code.

Insert a new step after StartDate and rename as MinDate.

  • Since we want the earliest date from Orders Table, call List.Min function on the Order Date column.
  • For the MinDate step, insert this ” = List.Min((Orders[Order Date]))”. This will look for the earliest date in the column called Order Date in the table called Orders. 

Once we have this, go back to the source and replace StartDate with MinDate. 

At this point, you should have something like below. Here the date table starts from 01/01/2015 which is the first order date in the Orders table. 

Lets us now say, that we wanted the EndDate to be the last ship date. This is also equally easy. Follow these steps and you will have a true dynamic date table whose dates are based on the dates in your model.

  1. Insert a new step after EndDate and rename as MaxDate.
  2.  Since we want the latest date from Orders Table, call List.Max function on the Ship Date column.
  3. For the MaxDate step, insert this ” = List.Max((Orders[Ship Date]))”. This will look for the latest date in the column called Ship Date in the table called Orders. 

At this point, you should have something like below. Here the date table ends on 07/07/2015 which is the second last ship date in the Orders table. To get the last ship date, just add 1 to the count step as follows. 

This way we can create a dynamic date table that fetches the StartDate and the EndDate using the data within our model.

I am including all the variations of the M code for easy reference.

 let
    Source = List.Dates(MinDate, Count, #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
    // We can manually choose the Start Date and the rest of calendar will populate based on this value
    StartDate = #date(2010, 1, 1),
    // this is taking the earliest date from Order Date column. 
    MinDate = List.Min((Orders[Order Date])),
    // this is taking end date as today's date
    EndDate = DateTime.Date(DateTime.LocalNow()),
    // this is taking the last Ship Date 
    MaxDate = List.Max((Orders[Ship Date])),
    Count = Duration.Days(MaxDate - MinDate) + 1,
    Custom1 = #"Changed Type",
    #"Inserted Year" = Table.AddColumn(Custom1, "Year", each Date.Year([Date]), Int64.Type),
    #"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([Date]), Int64.Type),
    #"Inserted Quarter" = Table.AddColumn(#"Inserted Month", "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
    #"Inserted Week of Year" = Table.AddColumn(#"Inserted Quarter", "Week of Year", each Date.WeekOfYear([Date]), Int64.Type),
    #"Inserted Day of Year" = Table.AddColumn(#"Inserted Week of Year", "Day of Year", each Date.DayOfYear([Date]), Int64.Type),
    #"Sorted Rows" = Table.Sort(#"Inserted Day of Year",{{"Date", Order.Descending}})
    
in
    #"Sorted Rows"

Let me know your thoughts on this. In the next post, we will continue our M Query journey.

Till next time !!!

Demystifying Date Dimension Part 3

Hello All,


Let us take a deep dive into another way of creating the Date Table. However, for the next few series of blog posts, we will focus on M Query and we will first work with static values and then modify the M Query to make it more dynamic.

Steps that you can follow along with me are as follows: 

We are using the sample data from the previous post. You can download the sample file and have a look at the first 2 parts of this series here and here.

Click on the edit queries/transform data.

Click on the new source and click on a blank query.

This should look something like this.

In the formula bar type in = List.Dates(#date(2020,1,1), 365, #duration(1,0,0,0)) and press enter.

This should have given you something like this.

I hope that you recall from previous posts the three main components that I spoke about, StartDate, EndDate, and the Duration. Once again I will reiterate that this is all that we need for any date table. How we get this is all about manipulating the code, but eventually, that is all we need to create a date table.

Based on this let us break this formula and understand each component of it.

  • The List.Date function takes in three parameters, start as date, count as the number, and duration as the increment.
  • The start is the beginning date or the date when my calendar should start. In this example, I am using the beginning of this year as the start.
  • The count is the number or how many such date instances do you want to have on this list. I am choosing 365 as my count. Be careful that this count is the number of days. It is for several instances. If in the duration we choose hours, then this instance will mean 365 such hour instance.
  • The step is the last parameter, it simply means what is the duration or increment of each instance. I am calling the function #duration and saying that I need an increment of a day, #duration(1,0,0,0).
  • Since we have used the function List.Date, this Query1 is a list.

Next, click on Transform and convert into a table and rename this as a query as MyCal. 

When you are prompted for the To Table dialog box, you can simply hit ok as there is no delimiter we need to worry about.

Next, rename this column to Date by double-clicking it and type in the word Date. Alternatively you can also right click this column and rename it. While you are at it, click on the small icon ABC/123 to change the data type to Date. 

At this point, you have a column called Date in the table called MyCal and the date ranges from 01/01/2020 to 12/30/2020.

But wait, what happened to 31st??? Remember we chose the count as 365 earlier. hence it took 365 days from the StartDate of 01/01/2020.

Do not worry as we will modify the code in a short while to include the last day of the year.

Let us continue expanding this table.

  • Click on Add Column in the ribbon.
  • Click on the down arrow under the Date icon.
  • Click on Year, this will add a new column called Year.
  • Similarly, we can add Month, Quarter, Week, and Day columns in our table. 

At this point, we should have a decent date table. However, this is very static and we still have not taken care of the 31st of this year. To take care of that, just change the count to 366 and you should have all the days of this year.
But this is very brutish (because in the Year 2021 we will have 365 days instead of 366), in the next blog we will make this table more dynamic.

The complete M code is as follows, to get the same result as above just click on new blank query and click on advanced editor and past the following.

let
Source = List.Dates(#date(2020,1,1), 366, #duration(1,0,0,0)),
#”Converted to Table” = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#”Renamed Columns” = Table.RenameColumns(#”Converted to Table”,{{“Column1”, “Date”}}),
#”Changed Type” = Table.TransformColumnTypes(#”Renamed Columns”,{{“Date”, type date}}),
#”Inserted Year” = Table.AddColumn(#”Changed Type”, “Year”, each Date.Year([Date]), Int64.Type),
#”Inserted Month” = Table.AddColumn(#”Inserted Year”, “Month”, each Date.Month([Date]), Int64.Type),
#”Inserted Quarter” = Table.AddColumn(#”Inserted Month”, “Quarter”, each Date.QuarterOfYear([Date]), Int64.Type),
#”Inserted Week of Year” = Table.AddColumn(#”Inserted Quarter”, “Week of Year”, each Date.WeekOfYear([Date]), Int64.Type),
#”Inserted Day of Year” = Table.AddColumn(#”Inserted Week of Year”, “Day of Year”, each Date.DayOfYear([Date]), Int64.Type)
in
#”Inserted Day of Year”

This should give you a final result like below.

In the next post we will continue with the same date table to make it dynamic. If you have questions/queries/comments please do not hesitate to reach out to me.

Power BI Date Dimension Demystified Part 2

Hello All,

Let us continue with our journey of demystifying the Date Table. In my last post, we learned to use CALENDARAUTO() function. We will continue today with the second DAX function called Calendar(). We will also look at how to extract other “relevant columns” such as year, month name, month number, etc. I will go over the following in this post

  1. Create a date column using the CALENDAR() function.
  2. Use multiple DAX formulas to extract/add these columns.
  3. Use one DAX formula to generate the entire Date Table that will include all these columns.
  4. Dynamic dates selection and usage.

CALENDAR() Function:

The CALENDAR() functions take two parameters, StartDate, and End Date, and returns a table with one column with all dates between the StartDate and EndDate.

For this exercise, let StartDate be 01/01/2020 and EndDate be 12/31/2020.

If you were following this series from Part 1 then your environment is already set and just follow along. If you are new and want to follow along with same dataset then please go through Part 1 and the Power Query

Let us begin by navigating to the Modeling tab, click on the New Table, and use the following code to create a column in this table, containing all the dates between the start and end date as shown below.

date table using calendar = CALENDAR(DATE(2020,01,01), DATE(2020,12,31))

Once you hit enter, you should have the Date Column in your model. To view it, click on the data tab on the left and you should be a single column in this table of Date/Time datatype ranging from 1/1/2020 to 12/31/2020 as shown below.

As you can see we have a very similar result to what we had in Part 1. Since in Part 1 we used CALENDARAUTO(), Power BI fetched the minimum (start-date) and maximum (end-date) automatically from our model. However, for the CALENDAR() function, we need to specify the StartDate and EndDate.

Once we have this column, we can then extract important and relevant information regarding the date. We will be extracting information such as Month Number, Year, Month Name, Weekday, Quarter, Second approach to Quarter, Week Number, Day Number of the Month, WeekDay Number of the week.

I have included all these code snippets as follows so you can follow along with me as I do this. All we need to do is after the Date Column is created (using the approach above), create a new column for each of the relevant details.

To create the Month Number: 

  1. Click on the Data tab on the left on the Power BI Desktop.
  2. Once you are in the Data tab, you should see the date table that we created above on the right under fields. 
  3. If you followed along then you should have a table named “date table using calendar“.
  4. Click on the new column icon.
  5. Enter the following formula in the formula bar. 
  6. Month Number = Month(‘date table using calendar'[Date])
  7. Press enter.
  8. Your new column named Month Number is ready.
  9. Follow the same process for Month Name, Year, Weekday, Quarter, Quarter Second Approach, Week Number, Day Number, Weekday Number.

Use the following code snippets to generate these columns. I have included the images of the next two columns as an example. 

We have already done the first and second below. 

  • date table using calendar = CALENDAR(DATE(2020,01,01), DATE(2020,12,31))
  • Month Number = MONTH(‘date table using calendar'[Date])
  • Month Name = FORMAT(‘date table using calendar'[Date], “mmmm”)
  • Year = YEAR(‘date table using calendar'[Date])
  • Weekday = FORMAT(‘date table using calendar'[Date], “dddd”)
  • Quarter = Var monthnum = MONTH(‘date table using calendar'[Date])

Return 

SWITCH

(

                    TRUE(),

                    monthnum <=3, “Q1”,

                    monthnum > 3 && monthnum <= 6, “Q2”,

                    monthnum > 6 && monthnum <= 9, “Q3”,

                    monthnum > 9, “Q4”

)

  • Quarter Second Approach = 

// storing the month number in a variable called mnth

var mnth = MONTH(‘date table using calendar'[Date])

// getting the quarters 

var myqrt = ((mnth -1)/3 ) + 1

// truncate this result using Trunc function and WITHOUT passing the optional params

var trnc = TRUNC(myqrt)

// concat the alpahbet Q to this result 

var addQ = “Q ” & trnc

// Retun this

return addQ

  • Weeknumber = WEEKNUM(‘date table using calendar'[Date])
  • Day Number of the month = DAY(‘date table using calendar'[Date])
  • Weekday number of the week = WEEKDAY(‘date table using calendar'[Date])

You should have the following columns in your date table by now.

Let us take a pause here and create these columns using the Part 1 approach. In that post, we used Calendarauto() function. Using the same approach let us create other columns. 

  1. Once again navigate to the Data tab. 
  2. Click the table named date table from calendarauto. 
  3. Click on the new column and enter the code snippets 

These are the code snippets for the CALENDARAUTO() function. Please note that the start date and end date will be different for both these functions. The reason being in CALENDAR() we are specifying the StartDate and EndDate, whereas in CALENDATAUTO() it identifies the start date and end date based on the model. We have covered the CALENDARAUTO() in detail in the previous post here.

// all the columns for the Date Table from the start

  • date table from calendarauto DAX = CALENDARAUTO()
  • Month Number = MONTH(‘date table from calendarauto DAX'[Date])
  • Month Name = FORMAT(‘date table from calendarauto DAX'[Date], “mmmm”)
  • Year = YEAR(‘date table from calendarauto DAX'[Date])
  • Weekday = FORMAT(‘date table from calendarauto DAX'[Date], “dddd”)
  • Quarter = Var monthnum = MONTH(‘date table from calendarauto DAX'[Date])

Return 

SWITCH

(

                    TRUE(),

                    monthnum <=3, “Q1”,

                    monthnum > 3 && monthnum <= 6, “Q2”,

                    monthnum > 6 && monthnum <= 9, “Q3”,

                    monthnum > 9, “Q4”

)

  • Quarter Second Approach = 

// storing the month number in a variable called mnth

var mnth = MONTH(‘date table from calendarauto DAX'[Date])

// getting the quarters 

var myqrt = ((mnth -1)/3 ) + 1

// truncate this result using Trunc function and WITHOUT passing the optional params

var trnc = TRUNC(myqrt)

// concat the alpahbet Q to this result 

var addQ = “Q ” & trnc

// Retun this

return addQ

  • Weeknumber = WEEKNUM(‘date table from calendarauto DAX'[Date])
  • Day Number of the month = DAY(‘date table from calendarauto DAX'[Date])
  • Weekday number of the week = WEEKDAY(‘date table from calendarauto DAX'[Date])

Awesome !!! we now have a date table with relevant data using both Calendar and Calendarauto approach. Let us dig a little deep into these DAX formulas that we have written. If you notice, we created multiple calculated columns based on the very first date column. This is okay to do so. However, we can combine all these separate DAX formulas into one.

For this let us create an entire date table using a single DAX formula and adding these columns within it. 

  1. Click on the modeling tab.
  2. Click on the new table icon.
  3. Paste the following code snippet.
  4. Once you press enter, your entire date table will be ready and populated. 

Date Using DaxCal Calendar =

// This variable is storing the column values

VAR DaxCal =

   CALENDAR ( DATE ( 2020, 01, 01 ), DATE ( 2020, 12, 31 ) )

RETURN

   // AddColumns returns a table with new columns specified by the DAX expressions

   ADDCOLUMNS (

     DaxCal,

     “Year”, YEAR ( [Date] ),

     “Month Number”, MONTH ( [Date] ),

     “Month Name”, FORMAT ( [Date], “mmmm” ),

     “Quarter”,

       “Q “

         & TRUNC ( ( ( MONTH ( [Date] ) – 1 ) / 3 ) + 1 ),

     “WeekNum”, WEEKNUM ( [Date] ),

     “Day of the Month”, DAY ( [Date] ),

     “Weekday Number of the Week”, WEEKDAY ( [Date] ),

     “Weekday Name”, FORMAT ( [Date], “dddd” ),

     “Full Date Only”, FORMAT ( [Date], “mm/dd/yyyy” )

   )

You should have a date table as shown below.

Let us break down the code.

  1. We are calling Calendar Function just like we did before. However this time we are storing the result in a Variable.
  2. We then want to return a table with other columns created using the DAX expressions.
  3. To do so we are calling the ADDCOLUMNS function.
  4. The first parameter we need to pass is the name of the table. In this case, it is our variable. I called it DaxCal.
  5. The next parameter is the name of the column. I called it Year.
  6. The last parameter is the DAX expression. In this case, it is YEAR([DATE]).
  7. Follow the same process till all the other columns have been called.

I have included the very similar code snippet for CALENDATAUTO() function that was used in Part 1

Date Using DaxCal Calendarauto = 

VAR DaxCal =

   CALENDARAUTO ()

RETURN

   ADDCOLUMNS (

     DaxCal,

     “Year”, YEAR ( [Date] ),

     “Month Number”, MONTH ( [Date] ),

     “Month Name”, FORMAT ( [Date], “mmmm” ),

     “Quarter”,

       “Q “

         & TRUNC ( ( ( MONTH ( [Date] ) – 1 ) / 3 ) + 1 ),

     “WeekNum”, WEEKNUM ( [Date] ),

     “Day of the Month”, DAY ( [Date] ),

     “Weekday Number of the Week”, WEEKDAY ( [Date] ),

     “Weekday Name”, FORMAT ( [Date], “dddd” ),

     “Full Date Only”, FORMAT ( [Date], “mm/dd/yyyy” )

   )

Though we have created a Date Table, we are not done yet. Let us improve on this approach. What we just did is what I call the brute force method of creating a Data Table using the Calendar function. What I mean by this is that we hardcoded the StartDate and EndDate values in this.

Let us take this further and make it dynamic. If you remember from my previous post, There are 3 Main Things we need. StartDate, EndDate, and Duration. Once again I will reiterate that please remember this as this will help us understand the concept of Date Table when we create the Date Dimension using M Query in the future posts.

By now we know that the function CALENDAR() requires StartDate and EndDate. For now, we handed it by providing static values. Let us make it more dynamic.

Let us say, that we needed our date table to start from the first Order date as the StartDate and the last ship date as the EndDate.

To do so we will declare two more variables as follows.

Date Using DaxCal Calendar Dynamic =
// we are getting the minimum of the Order Date and storing it in a variable
Var MinDate = MIN(OrdersData[Order Date])

// we are getting the latest Ship Date and storing it in a variable
Var MaxDate = MAX(OrdersData[Ship Date])

// Calling the Calendar function and passing into it, the two variables as the arguments and returning the result with relevant columns
VAR DaxCal =
CALENDAR(MinDate,MaxDate)
RETURN
ADDCOLUMNS (
DaxCal,
“Year”, YEAR ( [Date] ),
“Month Number”, MONTH ( [Date] ),
“Month Name”, FORMAT ( [Date], “mmmm” ),
“Quarter”,
“Q “
& TRUNC ( ( ( MONTH ( [Date] ) – 1 ) / 3 ) + 1 ),
“WeekNum”, WEEKNUM ( [Date] ),
“Day of the Month”, DAY ( [Date] ),
“Weekday Number of the Week”, WEEKDAY ( [Date] ),
“Weekday Name”, FORMAT ( [Date], “dddd” ),
“Full Date Only”, FORMAT ( [Date], “mm/dd/yyyy” )
)

With this we were able to use two DAX functions, CALENDAR() and CALENDARAUTO(), to create the date table with additional columns as needed. I hope that I was able to shed some light on these two DAX functions. In the file below you will find all the code snippets that we used thus far.

All the columns for the Date Table using CALENDAR()

// all the columns for the Date Table from the start -- CALENDAR()


date table using calendar = CALENDAR(DATE(2020,01,01), DATE(2020,12,31))

Month Number = MONTH('date table using calendar'[Date])

Month Name = FORMAT('date table using calendar'[Date], "mmmm")

Year = YEAR('date table using calendar'[Date])

Weekday = FORMAT('date table using calendar'[Date], "dddd")


Quarter = 
            Var monthnum = MONTH('date table using calendar'[Date])
            Return 
                SWITCH
				(
				
                    TRUE(),
                    monthnum <=3, "Q1",
                    monthnum > 3 && monthnum <= 6, "Q2",
                    monthnum > 6 && monthnum <= 9, "Q3",
                    monthnum > 9, "Q4"
                )


Quarter Second Approach = 

// storing the month number in a variable called mnth
var mnth = MONTH('date table using calendar'[Date])

// getting the quarters 
var myqrt = ((mnth -1)/3 ) + 1

// truncate this result using Trunc function and WITHOUT passing the optional params
var trnc = TRUNC(myqrt)

// concat the alpahbet Q to this result 
var addQ = "Q " & trnc

// Retun this
return addQ



Weeknumber = WEEKNUM('date table using calendar'[Date])

Day Number of the month = DAY('date table using calendar'[Date])

Weekday number of the week = WEEKDAY('date table using calendar'[Date])

All the columns for the Date Table using CALENDARAUTO()


// all the columns for the Date Table from the start -- CALENDARAUTO()


date table from calendarauto DAX = CALENDARAUTO()

Month Number = MONTH('date table from calendarauto DAX'[Date])

Month Name = FORMAT('date table from calendarauto DAX'[Date], "mmmm")

Year = YEAR('date table from calendarauto DAX'[Date])

Weekday = FORMAT('date table from calendarauto DAX'[Date], "dddd")

Quarter = 
            Var monthnum = MONTH('date table from calendarauto DAX'[Date])
            Return 
                SWITCH
				(
                    TRUE(),
                    monthnum <=3, "Q1",
                    monthnum > 3 && monthnum <= 6, "Q2",
                    monthnum > 6 && monthnum <= 9, "Q3",
                    monthnum > 9, "Q4"
                )


Quarter Second Approach = 

// storing the month number in a variable called mnth
var mnth = MONTH('date table from calendarauto DAX'[Date]
)

// getting the quarters 
var myqrt = ((mnth -1)/3 ) + 1

// truncate this result using Trunc function and WITHOUT passing the optional params
var trnc = TRUNC(myqrt)

// concat the alpahbet Q to this result 
var addQ = "Q " & trnc

// Retun this
return addQ


Weeknumber = WEEKNUM('date table from calendarauto DAX'[Date])

Day Number of the month = DAY('date table from calendarauto DAX'[Date])

Weekday number of the week = WEEKDAY('date table from calendarauto DAX'[Date])

In the next blog post, we will create Date Dimension using Power Query. If you have questions/queries/comments please do not hesitate to reach out to me.

Till next time !!!!

Power BI Date Dimension Demystified Part 1

Hello All,

Today let us take the first step towards demystifying Date Table journey. Date Dimension plays a very important role. Almost always there is a need to perform and derive time-based insights. Few ground rules must be adhered to when it comes to creating a robust date table. These are

  • The date table must contain unique values
  • The date table must contain no null values
  • The date table must contain contiguous date values (from beginning to end)

This series of articles will go over 10 different manipulations/iterations of how one can achieve this. We will also incorporate fiscal year and custom sort

As I see, we have two tools at our disposal for these date tables. Using DAX and using M Query.  

  1. Calendarauto DAX function
  2. Calendar DAX function
  3. Number.From M query
  4. List M query
  5. Parameters
  6. Dynamic Parameters
  7.  Invoking a function
  8. Sort based on the calendar month 
  9. Sort based on Fiscal year — approach 1
  10. Sort based on Fiscal year — approach 2

For any date table, we must have 3 values/properties present. The start date, the end date, and the duration. For the purposes of these articles, let us call them, 3P as we will be revisiting them again

The start date — This the first (earliest) date available. We can either enter this manually (using calendar() function — coming up in the next article) or it is calculated based on the model. 

The end date — This is the last (latest) date available. We can either enter this manually (using calendar() function — coming up in the next article) or it is calculated based on the model. 

The duration — This is the increment or step. Almost always this increment is “day”. For example, let us consider the month of June 2020. 

The start date would be 06/01/2020 — 1st of June 2020

The last date would be 06/30/2020 — 30th of June 2020

The duration would be each day. Therefore if we were to create a date-table we should have 30 unique dates for the month of June 2020. 

Let us begin with the CALENDARAUTO() DAX function. 

This function returns a table with a single column named “Date” that contains a contiguous set of dates. The range of dates is calculated automatically based on data in the model. 

This function has some considerations/rules associated with it, they are :

  1. The model MUST contain date-time values. 
  2. It does not take into account the date values based on the calculated table and calculated column.
  3. The earliest date is calculated based on the MinDate 
  4. The latest date is calculated based on the MaxDate 
  5. If we pass an argument (an integer between 1 and 12) then our date table range “shifts” by that step. 

Let us begin with our sample data. 

My other post (Power Query) talks about how to connect to this data. I will go over very quickly over this once again. But if you need to revisit Power Query again, click the icon below to do so.

  1. Click get data
  2. Click on Excel
  3. Navigate to the folder where the excel file resides
  4. Load the data (usually we would go with transform data as this is a good practice to ensure the quality is consistent. However this sample data is the right format as we need it to be in). 
  5. Navigate to modeling tab and click on the new table
  6. This should open the formula bar
  7. Name the table (I named it date table from calendarauto DAX and type in calendarauto(). My final formula is date table from calendarauto DAX = CALENDARAUTO() )
  8. This will create a date-table with a single column called date as seen below
  9. As you can see it took the MinDate, MaxDate, and duration automatically based on the rules above. 
  10. We did not need to pass any parameter as by default the parameter (fiscal year-end month) is 12 (December)

Now let us supply 3 as the argument to our function and see the “shift” in action. 

  1. Navigate to modeling tab and click on the new table
  2. This should open the formula bar
  3. Name the table (I named it date table from calendarauto with 3 and type in calendarauto(3). My final formula is the date from calendar auto with 3 = CALENDARAUTO(3) )
  4. This will create a date-table with a single column called date as seen below
  5. As you can see it took the MinDate, MaxDate, and duration automatically based on the rules above. 
  6. We passed 3 as a parameter and hence the MinDate became the end of 3rd month for the year before. Our original date started from 1/1/1995 and ended on 12/31/2025, by providing 3 as the parameter we shifted the start date. As per our rule that we discussed earlier, the MinDate is calculated based on the fiscal year-end month. 
  7. The fiscal year-end month is nothing but the parameter that we provide. Since by default the parameter (fiscal year-end month) is 12 (December) hence the MinDate was 1/1/1995. In this case, we provided 3 as the parameter so our start date should become 4/1/1994 and keeping the reference intact the end date is the end of March i.e. 3/31/2026

Our work here is not done yet. We have just a single column with date-time values. However, this is by far the most important piece if creating the entire date table. Once we have unique, non-null, date-time values (remember our rules above) we are able to extract all the other information out of it.

However, this is all we need to extract all the other information out of it. The steps to extract the various values such as month name, month number, day number, etc will be covered in the next post when we demystify the calender() function since extracting the information is exactly the same for both these approaches. 

I hope that I was able to demystify this for you. However, if you have any concerns/queries/doubts or feel that I have made an error about this please do not hesitate to leave a comment or send me the feedback. I will be more than happy to discuss this. 

PowerShell – Adding a Dataset

PowerShell Scripts for creating and adding a dataset, columns and table.

Add PBIDataset

New-PowerBIColumn ---- creates a column
New-PowerBITable ----- creates a table
New-PowerBIDataSet ---- creates a dataset
Add-PowerBIDataset ----- adds a dataset


$ is used to declare a variable

Creating a Dataset

PS C:\"your path here"> $col1 = new-powerbicolumn -Name Name -Datatype String
>> $col2 = New-PowerBIColumn -Name ID -DataType Int64
>> $col3 = New-PowerBIColumn -Name Gender -DataType String
>> $table_primary = New-PowerBITable -Name main_table -Columns $col1,$col2,$col3
>>
>> $col4 = New-PowerBIColumn -Name ID -DataType Int64
>> $col5 = New-PowerBIColumn -Name Date -DataType DateTime
>> $col6 = New-PowerBIColumn -Name Detail -DataType String
>> $col7 = New-PowerBIColumn -Name Result -DataType Double
>> $table_secondary = New-PowerBITable -Name secondary_table -Columns $col4,$col5,$col6,$col7
>>
>> $dataset = New-PowerBIDataset -Name Meansh_Sample -Tables $table_primary, $table_secondary
>>
>> Add-PowerBIDataset -Dataset $dataset -WorkspaceId "provide workspaceID"


{

	$ is used to define a variable 
	new-powerbicolumn --- inbuilt cmdlet
	new-powerbitable --- inbuilt cmdlet
	new-powerbidataset --- inbuilt cmdlet
	
	
define a columns first, requires name and datatype

define a table and required which columns will be part of it

define a dataset and requires which tables will be part of it as well as the name of the data set 
	
	Finally do 

Add-PowerBIDataset command and provide the id for the workspace ----- if no name is given then the dataset will be created in "MyWorkspace" 

else if the name is provided then it will be a part of that workspace.
	
}	


 $col1 = New-PowerBIColumn -Name ID -DataType Int64
>> $col2 = New-PowerBIColumn -Name Data -DataType String
>> $table1 = New-PowerBITable -Name SampleTable1 -Columns $col1,$col2
>> $col3 = New-PowerBIColumn -Name ID -DataType Int64
>> $col4 = New-PowerBIColumn -Name Date -DataType DateTime
>> $col5 = New-PowerBIColumn -Name Detail -DataType String
>> $col6 = New-PowerBIColumn -Name Result -DataType Double
>> $table2 = New-PowerBITable -Name SampleTable2 -Columns $col3,$col4,$col5,$col6
>> $dataset = New-PowerBIDataSet -Name SampleDataSet -Tables $table1,$table2

Custom Scripts – Power Shell

One of my close friend had this discussion with me about how to get list of workspaces with certain conditions and how to get those details using PowerShell script. So this following script was written to get conditional workspaces and displaying them.

While we were testing this, we realized that there was an error thrown due to number of calls the script was making. To handle that we can add start – stop as a gap timer to this to ensure there is no issue.

$worksapces = Get-PowerBIWorkspace -Scope Organization -All

Write-Host "Getting Workspaces"

$groupnames = @()

ForEach ($workspace in $worksapces) 
{if ($workspace.Type -eq "PersonalGroup") { continue }
if ($workspace.IsOnDedicatedCapacity) { continue }

$reports = Get-PowerBIReport -WorkspaceId $workspace.Id  -Scope Organization

if ($reports.Length -eq 0) { continue }

if ($reports.Length -gt 0) {
    Write-Host "$($workspace.Name) - $($workspace.Type) - $($workspace.State) - $($workspace.IsOnDedicatedCapacity) - $($reports.Count)"
}

$exportWS = New-Object -TypeName PSObject -Property @{
    Name = $workspace.Name
    Type = $workspace.Type
    State = $workspace.State
    IsOnDedicatedCapacity = $workspace.IsOnDedicatedCapacity
    ReportCount = $reports.Count
}
$groupnames += $exportWS
}
$groupnames | Export-CSV "you_chosen_path_entire_path_is_needed.csv"

Powershell Commands

Following modules are available

MicrosoftPowerBIMgmt — is the highest  level within this you can have following

1.Admin

2.Capacities

3.Data

4.Profile

5.Reports

6.Workspaces

To get staretd

Install-Module -Name MicrosoftPowerBIMgmt and then run the installation command for individual modules as needed

%d bloggers like this: