M is now the new way to get data in SSAS Tabular 2017

If you work with SSAS Tabular model, you have option to import from your Power Pivot/Power BI model. In most cases when we build Power Pivot/Power BI model we use Power Query to get data. Prior to SSAS 2017 you will have problems after the import because your tabular model doesn’t speak M so you will need to find a way to re-import data back to your Tabular model.

Capture

Good news is now you don’t have to because SSAS Tabular 2017 introduces the modern Get Data experience for models at the 1400 compatibility level, read more about what’s new in SSAS 2017 here. But bear in mind that you will need SQL server 2017 and set your Tabular compatibility level to 1400 to be able to use this new feature. I have tested with SQL server 2016 and it allowed me to make a connection to my SQL database and I can see my table list. But the story ends here as so you can’t import anything.

Capture

People talked about this for long time and finally Microsoft delivered this. Well done to the Dev team. THANK YOU! It’s a natural thing to happen as more and more people prototype a model in Power BI/Power Pivot and at some point you will want to import it to a Tabular model for various reasons such as data size limit in Power BI/Power Pivot etc.. Now the dust settled, what happens next for me, I guess is to do a deep dive into M. You probably heard about custom data connector and you can build a connector to get data from ANY data source you might have. In order to do that, you will need to know M, see this great post here to get you started if you are curious about this just like me 🙂

 

Advertisements

Every DAX Measure is Evaluated in a External Filter Context

This post is a bit technical and if you don’t know DAX very well you will have trouble understanding it. Nevertheless, read along to get yourself familiar with how DAX works. I have decided to write this because I sometimes forget about this very important concept that every DAX measure is evaluated in an external filter context. Hope that after writing this down, I will never ever forget about this.

I have recently helped one of my clients to optimize some DAX code. I thought it’s an interesting journey and you may learn something from this as well. Before I dive into the problem, let me briefly tell you what DAX is and how important it is if you use Excel Power Pivot or Power BI desktop or SSAS Tabular. Data Analysis Expressions (DAX) is a library of functions and operators that can be combined to build formulas and expressions in Microsoft SQL Server Analysis Services (SSAS) Tabular Model, Power Pivot in Excel, and Power BI Desktop. If you are a business person as opposed to IT and you know DAX very well and also have good understanding of dimensional modeling, you are one foot in the door of potentially becoming a MS SQL BI developer. If you are planning to learn DAX, I highly recommend this book. Excel Power Pivot, Power BI desktop and SSAS Tabular all have the same engine (VertiPaq engine) under the hood, so if you know how to use one, you won’t have too much trouble using the other two.

The problem my client had is he wants to check a measure, say sales $ for the last 12 quarters (inclusive of the current one) and see if they all have sales $>0. If this is true then do X otherwise returns a blank value. For example, for Q3 2016, he needs to check if Q3 2016 and 11 quarters prior all have sales $>0. The way he wrote this is

IF [Sales $ for current quarter] >0 and [Sales $ for prior quarter]>0 and [Sales $ for prior 2 quarters] and [Sales $ for prior 3 quarters]>0……[Sales $ for prior 11 quarters]>0 THEN do X ELSE do nothing. All these measures are calculated using time intelligence function DATESINPERIOD. This takes roughly 30 seconds to return result if you filter your date table to 1 quarter. If you don’t filter your dates table prior it will take very long time to return result. The reason why this is slow because it needs to calculate 12 values for each quarter until then it can decide what to do next. The way I solve the same problem is for each quarter, I dynamically build a virtual table like below, then I filter this table to return rows where Sales $ >0, finally count number of rows of this filtered table to see if row count = 12. Because the existence of relationship between Dates table and Sales table, building such virtual table is extremely fast and filtering a table with only 12 rows is also extremely fast, so it will perform much better than the original approach.

Capture

The first measure I wrote was this.

Capture

 

When I tested it, it returned value of 1 for all rows. I overlooked the fact that this was evaluated in an external filter context. Meaure1 removes any external filter on Dates[QuarterID] column by way of ALL(Dates[QuarterID]), but Dates[Year] and Dates[Quarter] columns still in the view and Meaure1 will need to respect this filter. So for row 1 the virtual table I was trying to build only has one row which is 2014 Q4, hence it returned value of 1.

Capture

If I remove Dates[Quarter] column and add Dates[QuarterID] column, the number it returned changed. For row with Dates[QuarterID]=201703, the virtual table has 12 rows (201703,201702,201701,201604,201603,201602,201601,201504,201503,201502,201501,201404). because Dates[Year]=2017 is also in the view so the virtual table actually only has 3 rows (201703,201702,201701),hence value of 3 is returned.

Capture

If I remove Dates[Year], it returned expected result. However for Meaure1 to work you can only have Dates[QuarterID] in the view for it to return correct result.

Capture

A better way to rewrite this formula is as shown below.

Capture

And you can see it returned correct result with or without Dates[QuarterID] column.

Capture

DAX is a powerful language and you can do magic when you truly master it. It won’t be an easy journey and I can assure you that once you get hang of it, lots of impossibles will be possible just like what this guy has done it the world of soccer.

messi2

Timeline Storyteller custom visual for Power BI

Microsoft has recently introduced timeline storyteller for Power BI and you can read more about it here. If you have lots of events data, you can use it to visualize your data in a very fashionable way. Also I haven’t used the “Publish to web” feature to share any viz with general public so I decided to create a timeline story for soccer star Lionel Messi (you probably guessed by looking at my profile photo that I am a big fan of soccer.

If you haven’t used custom visual before, you have to add it to Power BI first. Click on either of 2 below buttons to add custom visual.  If you click the first one “From Store”, it will launch a new window and from there you can choose your interested custom visual and add it to your Power BI viz type panel. I think the second button “From File” will go eventually. A couple of months back, you have to go to Power BI’s custom visual page (now it’s been integrated into office store) to download custom visual and then use the “From File” button to add them. With this new “From Store” button, the Power BI team has made it simpler to add custom visual and you can do so without even leaving Power BI desktop.

1

Ok, enough said about custom visual. Strongly suggest you go to office store to have a look at what custom visual are there to help you better visualize your data.

Once you finish building your dashboard in Power BI desktop, you need to publish it to powerbi.com. Navigate to the relevant report page and go to “File” then choose “Publish to web”.

Capture

Then you will see below screen. The string in the second box is the code you need to embed in your html code.Capture2

Now you can head to below page to see my Lionel Messi’s timeline story.

https://app.powerbi.com/view?r=eyJrIjoiZTA1NmMwMTAtNzNmMC00MTY3LWJmZjctYjE3NjlkNDlkZjlkIiwidCI6IjBiNzRmNjIwLTY0MTctNGMwYi05NjcwLWJlODI2NmIzOTkzMCIsImMiOjEwfQ%3D%3D

More Ways to Add Date Dimension to your data model

This post is an extension from my last post about how to use Power Query M script to add a date dimension to your data model. you can read it here to learn more about why you need a separate date dimension table on top of the one generated internally by Power BI. This post I want to provide a couple of more options to add date table so you can pick one best suits your needs.

First option is you can use Calculated Table (to my knowledge this is only possible in Power BI desktop and you can’t create calculated table in Excel) to do this. Below is the DAX code to get you started and some sample data to show you how it looks like in Power BI desktop.

Calendar Table =
VAR Days = CALENDAR ( DATE ( 2015, 1, 1 ), TODAY() )
RETURN ADDCOLUMNS (
Days,
“Year”, YEAR ( [Date] ),
“Quarter”,SWITCH(MONTH ( [Date] ),1,”Q1″,2,”Q1″,3,”Q1″,4,”Q2″,5,”Q2″,6,”Q2″,7,”Q3″,8,”Q3″,9,”Q3″,”Q4″),
“Month Number”, MONTH ( [Date] ),
“Month”, FORMAT ( [Date], “mmmm” ),
“Week”,WEEKNUM([Date],2),
“Year Month”, FORMAT ( [Date], “mmm yy” )
)

1

The second option, a better one is the recent contribution from Miguel Angel Escobar. Imagine if you can find a button in the “Get Data” page of Power BI desktop like below. Wow, that’s cool and very convenient.

2

You can find more about it from his blog post here. He gave you options to add a standard date table with both calendar and fiscal dates as well as a calendar table based on the 4-4-5, 4-5-4, and 5-4-4 patterns as shown in the above picture. Be sure to check out the comment area as well as you will find useful info from there too.

Power BI template with built in Date dimension

If you are serious about building Power BI solutions, you definitely need a Date dimension in your data model. Reza Rad had written a fantastic blog about why need this, you can find it here. Isn’t it good that you can have a template file that already has a built in Date table and you can use this as a starting point to build your data model. As you can see below, this table has all the dates from a starting date (defined by you) and up until yesterday. Plus it is dynamic so it will update as time goes by when you refresh your data model. I have built a Power BI template file so when you open it or import it you will have a built in Date table. You will find the download link at the end of this post.

Date.JPG

If you open the query editor and go to Advanced Editor, you will see the underlining M code as below

//Create Date Dimension
(StartDate as date)=>
let
//Capture the date range from the parameters
StartDate = #date(Date.Year(StartDate), Date.Month(StartDate),
Date.Day(StartDate)),
EndDate = Date.From(DateTime.LocalNow()),
//Get the number of dates that will be required for the table
GetDateCount = Duration.Days(EndDate – StartDate),
//Take the count of dates and turn it into a list of dates
GetDateList = List.Dates(StartDate, GetDateCount,
#duration(1,0,0,0)),
//Convert the list into a table
DateListToTable = Table.FromList(GetDateList,
Splitter.SplitByNothing(), {“Date”}, null, ExtraValues.Error),
//Create various date attributes from the date column
//Add Year Column
YearNumber = Table.AddColumn(DateListToTable, “Year”,
each Date.Year([Date])),
//Add Quarter Column
QuarterNumber = Table.AddColumn(YearNumber , “Quarter”,
each “Q” & Number.ToText(Date.QuarterOfYear([Date]))),
//Add Week Number Column
WeekNumber= Table.AddColumn(QuarterNumber , “Week Number”,
each Date.WeekOfYear([Date])),
//Add Month Number Column
MonthNumber = Table.AddColumn(WeekNumber, “Month Number”,
each Date.Month([Date])),
//Add Month Name Column
MonthName = Table.AddColumn(MonthNumber , “Month”,
each Date.ToText([Date],”MMMM”)),
//Add Day of Week Column
DayOfWeek = Table.AddColumn(MonthName , “Day of Week”,
each Date.ToText([Date],”dddd”))
in
DayOfWeek

The above M code creates a function and by invoking it after you specify a start date, you will get a nice date table for your data model. If you need additional columns such as fiscal year you can either amend the M code to add it or you can do so after loading this table into your model first. Entirely up to you.

Date2.JPG

You can download the Power BI template file here

 

 

Excel UNICHAR function is also in Power BI

Back in the old days I remember I had used Excel REPT function to hold a bar in a cell like the graph below (I can’t recall why I did this), but I am sure there is some great use of this trick out there. You can see the formula in the formula bar. You also need to set the font to Britannic Bold for this to work.

excel rept and unichar

Good news is now you can use the same trick in Power BI as well . Chris Webb has recently blogged about this. Go check it out.

The DAX Unichar() Function And How To Use It In Measures For Data Visualisation

 

2 great new features in Power BI April update (quick measures and add column by example)

Microsoft released Power BI April update a few days ago. I am very excited about 2 new features -quick measures and add column by example. These 2 new features are great for Power BI beginners because now you can add measures and custom columns without needing to know DAX and Power Query M language. Power BI will automatically create them for you. It’s also a great way of learning DAX and M by looking at the code generated by Power BI.

You can create a quick measure by selecting Quick measures from the field menu, either in the field of a chart or from the field list.

quickmeasures

In the dialog, you can pick any of the 19 different calculations to perform on the selected measure. Depending on the calculation, you’ll have different required parameters you’ll need to fill in. You can also change the fields being used in the calculation using the field list on the right half of the dialog. Click below link for more detailed info on this feature.

https://powerbi.microsoft.com/en-us/blog/quick-measures-preview/preview/

The add column by example feature is really like Flash Fill introduced in Excel 2013. It works like by manually typing some sample values you want then Power BI will apply the same transformation rule to other rows of the same column. The more sample values you type the more accurate it will get. Watch below video for a complete run down of how it’s doing the magic.

P.S. Quick measure is a preview feature so you need to go to option menu to enable it

Click File menu in your ribbon and choose Options and Settings, click on Options to open Options dialog, then click Preview features to enable it