March of change

Well the March 2017 Power BI Desktop update has arrived and all I can say is wow!

Matrix Preview is just that, suddenly I was back in the late 90’s watching “bullet time” for the first time.
“Wait you can make a functional matrix table now that can also be used to replace a load of slicers?” Mind was blown!

Themes seem like such a minor thing, but as soon as you try and undertake corporate work you end up spending more time changing the colour scheme all the time to meet your corporate standards. Suddenly you can do it all easily and save yourself hours. Great feature, but keep adding to this guys, there’s more we need.

I’ll do more posts on these features as they evolve.

Merry New Year!!!

As we start a new year I can’t help but feel that elements of comedy will be involved… actually I really hope they will be. On a personal note I’ve signed up already for two of the four events I will be running this year; The Wall and The Great North Run, next will be the Edinburgh Ultra Trail and the Gateshead 10km. I love my challenges and am looking forward to them all! This year I won’t be running for any charities, but I would ask that you donate to your local blood bank, either blood, plasma, platelets or cash.

Power BI in 2017 – so after suffering some set backs and there being issues with the way that the current Enterprise On-Premise Gateway is implemented I had to call a halt to Direct Query testing. To be honest I can see Direct Query being a blessing and a curse to an organisation. Some of our use cases will need Direct Query to be fully realised – mostly operational reporting. But our traditional reporting really benefits from the Power BI. I’ve lost count of the number of calls I’ve had recently that have felt like infomercials. “Oh you want to report on that by something different, that’s no problem with Power BI it’s a snap” and while privately I do throw up in my mouth a little at the sheer cheese factor, I’m blown away each and every time as are my colleagues.

Maybe starting the post and thinking of doing something drawing parallels to Trading Places was a stretch too far, but I do feel ahead of the game now. Experience and many scars teach us that no matter what is produced they’ll always want more, but now instead of running around frantic like the other guys in the stock exchange I’m calm and collected. So I’ll end with a quote from the cinematic classic that is Trading Places

“Looking good, Billy Ray!”

“Feeling good, Louis!”

Direct Query: Power BI on drugs… probably sedatives

Direct Query: Power BI on drugs… probably sedatives

It’s no secret that I think Power BI is great, even on days like today when Power BI has well and truly shafted me. Half the day has been lost by crashes and everything is getting tense, but still Power BI is going to deliver. The biggest challenge you’ll face with Direct Query is that it is only able to do a subset the things that your normal “full fat” Power BI deployment can do. As you no doubt read in my other post “A Plan Forms” lots of businesses will like Direct Query, certainly as a day one proposal when starting in Power BI.

Once you start down the Direct Query route be prepared to consider that you have to make sure there’s plenty of headaches on the road to victory. Undertaking it though is well worth it. I will continue to keep you informed of my progress and you can sneer at my mistake and hopefully we can have some thigh slapping moments together.

 

Upwards and Onward my friend

So you need to do a comparison across time periods, this has always slightly confused me I’ll be honest. Earlier though I had to figure it out, basically the requirement was to work out what happen in the last month and in the last three months. The idea being to understand if things are improving over time or (please don’t be the case) getting worse.

Now with all the time intelligence that’s out there in Power BI it is really easy to do much of this and DATESINPERIOD() is a real friend. I will no doubt end up adding another tag for Time Intelligence formulae or methods because they really are the ultimate glue that holds most of Power BI or PowerPivot together. Let’s face it half of the time you want to compare things over time don’t you?

DATESINPERIOD(<Possible Date Values (column of dates)>,<Context Date for reference>,<periods for range>, <Period selection>

with Time intelligence it is really important to make sure that you have a dates table. Recent advances in Power BI mean your date table can be much simpler, but you still need to think about what exists in that table and what features are a must from there.

The context date is really the pivot around which Dates in period works I don’t want to call it a start date because you can go forward in time from there or backwards it is the starting point, but I find that simplistic.

Periods are the key here, firstly how many if that number is positive then you will count on that many periods from the context date if it is negative you will be counting backwards from the context date. Possible periods are Day, Month, Quarter, Year

So let us see a sample

=Calculate([Measure],DATESINPERIOD(Dim_Calendar[Date],[SpecificDate],-3,MONTH)

So break this down…

  1. Calculate – Basically work this all out and a return a single (scalar) value
  2. [Measure] – What are you actually wanting to do, you could call an existing measure or write one here
  3. DATESINPERIOD(Dim_Calendar[Date] – return a list of dates from the column
  4. [SpecificDate] – Context value a date that will be used to reference the periods against
  5. -3, MONTH – Month refers to the period and a count of those in this case -3 so go back three month

The clever bit for me was to get the [SpecificDate]

=ENDOFMONTH(DATEADD(Dim_Calendar[Date],-1,MONTH)

What was that?

Break it down, basically it returns the last day of the previous month (with data), typically you run this against a transaction table to try and get the correct context, but I’m sure you could run it against your Dim_calendar table, I just did it differently in this example what are we doing

  • Find the single value (scalar) for the last date in the column
  • Filter the column to be a month ago

The worst possible thing about time intelligence is it mostly isn’t compatible with Direct Query.

Our new home

Our new home

So we got a new site and with it a new home, now all of a sudden I’m doing things properly, I have a proper website with a grown up URL and everything. I hope you enjoy your visit and drop me a line.

So after six months or so of sitting about and not really moving forward I took the advice of a good friend and pulled the trigger. We’re now here at www.bi4csi.com. I hope the change isn’t too much for any of you. Feel free to message me and send virtual house warming gifts

“Hello World”