Tag: DAX

Data Visualisation, DAX, Power BI, Power BI Desktop, Power Query, Self-Service BI

Integrating and Visualising Multiple Microsoft To Do Accounts with Power BI

Integrating and Visualising Multiple Microsoft To Do Accounts with Power BI

It’s been a while that I use Microsoft To Do to organise my daily tasks. From work-related tasks to buy groceries. While Microsoft To Do is super easy to use but there are some challenges in using it more efficiently, especially when you have multiple O365 accounts within different organisations. Here are some of the challenges I faced; you may face other challenges too:

  • The Microsoft To Do app for Windows devices is very user friendly with amazingly good features like the ability to add multiple To Do accounts. However, we currently have to select which account we would like to use and the app shows all our tasks within that specific account. This means we can not see all our tasks from all our accounts in a single place.
  • The Microsoft To Do app for iOS devices is also very handy to use, but it lacks adding multiple accounts. Hence we cannot see all our tasks from multiple O365 accounts on the app. 🙁
  • We can use the Tasks within the Microsoft Outlook desktop application (I used the Windows version) which is by far the most comprehensive one with tons of features. While we can see tasks from multiple accounts in a single place, it is a real challenge if I want to know which task is assigned to which account. Besides, it is really hard to answer some questions like, how many high-priority tasks I have for today or the week ahead. I know, we can group tasks, but, it is still not so intuitive.

For the above reasons, I searched for a product that can do all the above at once. After spending some hours, I thought, well, I have to do it myself.

With that, let’s go ahead and see how we can get the job done in Power BI.

Note:

This method is not working for Microsoft To Do using personal accounts such as Outlook, Hotmail or MSN. If anyone knows how to add those, please let us know in the comments section below this post.

This is a long post that took me a reasonable amount of time to write. So I added the following table of contents so you can quickly jump to a subject of your interest.

Table of Contents

How It Works

Microsoft Power BI is NOT a reporting tool only. We can connect to many data sources, mix and match the data, create data models and visualise the data. So it should be possible to connect to multiple To Do accounts, append the data, create a simple data model on top of that, and visualise the data to answer our questions or our customers’ questions. The Microsoft To Do data is accessible via the Microsoft Exchange Online connector available in Power BI. The rest depends on our requirements and what questions we would like to answer.

In my case, in which I am the end-user of the report, I would like to be able to know:

  • Today’s tasks: All tasks that their StartDate or DueDate is today or the Tasks without any StartDate and DueDate
    • Number of tasks
    • Number of important tasks
    • Tasks by mailbox
    • Tasks details
      • Task list
      • Task description
      • Status
      • Start date
      • Due date
      • A link to the task itself that I can update if I want to
  • All Tasks
    • All above plus
      • Number of open tasks
      • Number of completed tasks

You or your customer(s) might have different requirements, but once you understand how to get the To Do data from Microsoft Exchange Online and do some data explorations to find out what you are after, you’ll be good.

Continue reading “Integrating and Visualising Multiple Microsoft To Do Accounts with Power BI” →
Azure Analysis Services, BISM Tabular, Business Intelligence - BI, Data Warehousing - DW, DAX, Power BI, Power BI Desktop, Power BI Desktop RS, Power Pivot, Power Query, Quick Tips, Self-Service BI, SQL Server, SQL Server Analysis Services - SSAS, T-SQL

Quick Tips: Time Dimension with Time Bands at Seconds Granularity in Power BI and SSAS Tabular

Time Dimension with Time Bands at Seconds Granularity in Power BI and SSAS Tabular

I wrote some other posts on this topic in the past, you can find them here and here. In the first post I explain how to create “Time” dimension with time bands at minutes granularity. Then one of my customers required the “Time” dimension at seconds granularity which encouraged me to write the second blogpost. In the second blogpost though I didn’t do time bands, so here I am, writing the third post which is a variation of the second post supporting time bands of 5 min, 15 min, 30 min, 45 min and 60 min while the grain of the “Time” dimension is down to second. in this quick post I jump directly to the point and show you how to generate the “Time” dimension in three different ways, using T-SQL in SQL Server, using Power Query (M) and DAX. Here it is then:

Time Dimension at Second Grain with Power Query (M) Supporting Time Bands:

Copy/paste the code below in Query Editor’s Advanced Editor to generate Time dimension in Power Query:

let
Source = Table.FromList({1..86400}, Splitter.SplitByNothing()),
#"Renamed Columns" = Table.RenameColumns(Source,{{"Column1", "ID"}}),
#"Time Column Added" = Table.AddColumn(#"Renamed Columns", "Time", each Time.From(#datetime(1970,1,1,0,0,0) + #duration(0,0,0,[ID])), Time.Type),
    #"Hour Added" = Table.AddColumn(#"Time Column Added", "Hour", each Time.Hour([Time]), Int64.Type),
    #"Minute Added" = Table.AddColumn(#"Hour Added", "Minute", each Time.Minute([Time]), Int64.Type),
    #"5 Min Band Added" = Table.AddColumn(#"Minute Added", "5 Min Band", each Time.From(#datetime(1970,1,1,Time.Hour([Time]),0,0) + #duration(0, 0, (Number.RoundDown(Time.Minute([Time])/5) * 5) + 5, 0)), Time.Type),
    #"15 Min Band Added" = Table.AddColumn(#"5 Min Band Added", "15 Min Band", each Time.From(#datetime(1970,1,1,Time.Hour([Time]),0,0) + #duration(0, 0, (Number.RoundDown(Time.Minute([Time])/15) * 15) + 15, 0)), Time.Type),
#"30 Min Band Added" = Table.AddColumn(#"15 Min Band Added", "30 Min Band", each Time.From(#datetime(1970,1,1,Time.Hour([Time]),0,0) + #duration(0, 0, (Number.RoundDown(Time.Minute([Time])/30) * 30) + 30, 0)), Time.Type),
#"45 Min Band Added" = Table.AddColumn(#"30 Min Band Added", "45 Min Band", each Time.From(#datetime(1970,1,1,Time.Hour([Time]),0,0) + #duration(0, 0, (Number.RoundDown(Time.Minute([Time])/45) * 45) + 45, 0)), Time.Type),
#"60 Min Band Added" = Table.AddColumn(#"45 Min Band Added", "60 Min Band", each Time.From(#datetime(1970,1,1,Time.Hour([Time]),0,0) + #duration(0, 0, (Number.RoundDown(Time.Minute([Time])/60) * 60) + 60, 0)), Time.Type),
    #"Removed Other Columns" = Table.SelectColumns(#"60 Min Band Added",{"Time", "Hour", "Minute", "5 Min Band", "15 Min Band", "30 Min Band", "45 Min Band", "60 Min Band"})
in
    #"Removed Other Columns"
Continue reading “Quick Tips: Time Dimension with Time Bands at Seconds Granularity in Power BI and SSAS Tabular” →
Business Intelligence - BI, Data Visualisation, Power BI, Power BI Desktop, Power BI Service, Self-Service BI

Highlighting Below Avg Sales per Hierarchy Level with SWITCH() and ISINSCOPE() DAX Functions in Power BI

Highlighting Below Avg Sales per Hierarchy Level with SWITCH() and ISINSCOPE() DAX Functions in Power BI

I was working on a project a wee bit ago that the customer had conditional formatting requirement on a Column Chart.
They wanted to format the columns in the chart conditionally based on the average value based on the level of hierarchy you are at.
Here is the scenario, I have a Calendar hierarchy as below:

  • Calendar Hierarchy:
    • Year
    • Semester
    • Quarter
    • Month
    • Day

I use “Adventure Works DW2017, Internet Sales” Excel as my source in Power BI Desktop. If I want to visualise “Total Sales” over the above “Calendar Hierarchy” I get something like this:

Line Chart in Power BI, Total Sales by Year

Now I activate “Average Line” from “Analytics” tab of the Line chart.

Adding Average Line to Line Chart in Power BI

When I drill down in the line chart the Average line shows the average of that particular hierarchy level that I am in. This is quite cool that I get the average base on the level that I’m in code free.

Power BI, Drilling Donw in Line Chart

Easy, right?

Now, the requirement is to show the above behaviour in a “Column Chart” (yes! visualising time series with column chart, that’s what the customer wants) and highlight the columns with values below average amount in Orange and leave the rest in default theme colour.

So, I need to create Measures to conditionally format the column chart. I also need to add a bit of intelligent in the measures to:

  • Detect which hierarchy level I am in
  • Calculate the average of sales for that particular hierarchy level
  • Change the colour of the columns that are below the average amount

Let’s get it done!

Detecting Hierarchy Level with ISINSCOPE() DAX Function

Microsoft introduced ISINSCOPE() DAX function in the November 2018 release of Power BI Desktop. Soon after the announcement “Kasper de Jonge” wrote a concise blogpost about it.

So I try to keep it as simple as possible. Here is how is works, the ISINSCOPE() function returns “True” when a specified column is in a level of a hierarchy. As stated earlier, we have a “Calendar Hierarchy” including the following 5 levels:

  • Year
  • Semester
  • Quarter
  • Month
  • Day

So, to determine if we are in each of the above hierarchy levels we just need to create DAX measures like below:

ISINSCOPE Year		=	ISINSCOPE('Date'[Year])
ISINSCOPE Semester	=	ISINSCOPE('Date'[Semester])
ISINSCOPE Quarter	=	ISINSCOPE('Date'[Quarter])
ISINSCOPE Month		=	ISINSCOPE('Date'[Month])
ISINSCOPE Day		=	ISINSCOPE('Date'[Day])

Now let’s do an easy experiment.

  • Put a Matrix on the canvas
  • Put the “Calendar Hierarchy” to “Rows”
  • Put the above measures in “Values”
Detecting Year, Semester, Quarter, Month and Day hierarchy levels with ISINSCOPE in Power BI Desktop

As you see the “ISINSCOPE Year” shows “True” for the “Year” level. Let’s expand to the to the next level and see how the other measures work:

Continue reading “Highlighting Below Avg Sales per Hierarchy Level with SWITCH() and ISINSCOPE() DAX Functions in Power BI” →
Business Intelligence - BI, DAX, Power BI, Power BI Desktop, Power Query, Quick Tips, Self-Service BI

Quick Tips: How to Sort Matrix by Column in Descending Order

How to Sort Matrix by Column in Descending Order

Today Microsoft released Power BI Desktop March 2020 which I was hoping that it includes a simple feature on Matrix visual to be able to sort the Martix by column in descending order, but, it doesn’t. So, in this post I quickly show you how to sort Matrix by column in descending order.

Here is the scenario. One of my customers is building a report in Power BI showing sales by Year, Month and Day of Week in a Matrix as below.

Sorting Matrix Visual in Power BI
Sorting Matrix Visual in Power BI

Everything looks fine! But looking at the Matrix sorting quickly reveals that such feature is NOT available (YET). But the customer would like to see the Matrix sorted by Year in descending order, something like this.

Sorting Matrix by Column Headers in Descending Order
Sorting Matrix by Column Headers in Descending Order

Here is the solution which is super simple.

Continue reading “Quick Tips: How to Sort Matrix by Column in Descending Order” →