Category Archives: Self-Service BI
ALL(‘the Bengal Ladies’)
All the BENGAL Ladies…
Today’s post goes out to all my DAX Tigress friends out there… put your hands up! AND, to my fellow meme enthusiasts – I have a meme trifecta for you today (cats + pugs + ???).
Interestingly, I recently watched #CATS_THE_MEWVIE, a documentary on Nextflix, and learned about the history and evolution of cats on the internet – quite fascinating! The link to Google Trends & chart below does validate that cats do trend higher in interest over dogs!
https://trends.google.com/trends/explore?date=today%203-m&geo=US&q=cat%20meme,dog%20meme
Let’s talk about another STRANGE phenomenon that exists …
- Our Power BI classes are > 50% female.
- The best student in each class is also often female.
- Applicants for P3 Principal Consultants are > 95% male!
Yeah. That’s how I feel! What gives? Well done Pugs! It’s impossible to know exactly why, but let me share MY story.
First, let’s start with TODAY. I’m one of two Directors here at P3. I started as a Principal Consultant in April-2017 and was promoted to Director in Jul-2019. At the time, I wasn’t sure that I was Director material! But 6-months into the new role, I’m wondering why I ever even doubted myself!
So, one might say… I’m an example of success, yeah? Even though it’s HARD for me to say that.
Well, the road to get here was filled with self-doubt. And just in case that’s what YOU are feeling these days, let me share that road.
Growing up, I was the little sister of a big brother that was the salutatorian of his graduating class! At the start of the year, my teachers would ALWAYS ask… “Are you Bernie’s little sister?” Implying that they expected perfect straight A’s… Ugh!
In college, there were no more worries about walking in the shadows of my super-smart big brother! But, I noticed that there were not many other women in my Computer Science classes. However, one of my instructors predicted that women would be needed in Computer Science – his insights were correct, and I am thankful for his encouragement.
My very first internship was for a government contracting company in Alexandria, VA – my clients for the project assignment were military personalities.
None of these early experiences were especially ideal for fueling a young person’s self-esteem. Or were they?! I for sure didn’t get salutatorian in high school. I did not graduate Summa Cum Laude! And yes, I was VERY intimidated by those military personalities!
But something I did do was put myself out there and just TRY! Each time you try, you learn and when you learn you grow. The more you grow, the more experiences you will have. You just have to learn to believe in yourself!
I graduated from high school with good grades. I graduated from college with a Computer Science minor and Mathematics major. I completed the assigned government project, an additional project and received a very nice referral from the company to launch my career in the adult world!
Fast forward to 7 years ago in 2013, I remember being in my office cubical upgrading to Excel 2010 and coming across the new Power Pivot add-in. Through a Google search, I came across “THIS” website. “THIS” website helped me to see what was possible with Power Pivot and that other people “out there” saw something special too! But, I still wasn’t confident that this could be a new emerging career path for me. Organizational leadership at the time said there were “other”, “better” BI tools. I thought, maybe they knew something I didn’t know!? Again, there was self-doubt.
But I kept going and learning because of “THIS” website, because of the others out there writing the blog posts. Those people were validating what my gut was telling me, but I STILL was not quite as confident as those paving the way.
My gut told me…“THIS” tool IS different, and I thought to myself, “THIS” blog website IS going to become a business… and one day, when that happens, I want to be a part of “THIS” new way. I want to HELP others with data, reporting, analytics and these quickly emerging technologies! It was just a thought, an idea, a wish, a DREAM.
All the while, I kept taking tiny steps… I subscribed to “THIS” blog, I contacted Rob/Jocelyn about training, I continued to self-teach when our organization didn’t have the budget for training. I attended free webinars. I kept applying what I could, even in a not so perfect hodge-podge of SQL + Power Pivot In Excel + One Drive toolsets. I went to user group meetings and met many helpful mentors. I spent my evenings reading/learning and my days at work shifting what I was doing from the old way to “THIS” new way.
… and when I saw a blog post in February of 2017 that P3 was hiring full-time consultants, I shifted all my energy to updating my resume and applying, still not feeling overly confident in myself. Why? Because surely there are many other people out there that are smarter, more experienced, etc.
However, it turns out that I was one of the top 3% able to complete the hiring testing and I got the job – yea me! Followed by “THIS” once in a lifetime experience at P3 of HELPING others – not only with Power BI but with the confidence that their gut IS also right.
Confidence is believing in yourself! Surrounding yourself with people that support and believe in your DREAMS. You get there by taking small steps… and it’s important so that you can change, grow and see what YOU can accomplish if you just try!
So, whether you’re thinking of applying for a job here, or just wondering whether you “belong” at the table in your current firm, think about MY story and how you never know what might be possible until you just put yourself out there and try! BELIEVE in YOUrself and never give up on your dreams! One life… dream, believe and live it!
Have a healthy, happy & confident 2020!
Oh, and just for the record –
- I’m more of a J. Lo versus Beyoncé fan!
- But if the lyrics fit, I must do it!
- Animal meme blog trifecta complete… cats (2018) + pugs (2019) + inspirational blue jay (2020)
- Feel free to find me on LinkedIn – always happy to connect!
- Lastly, don’t be afraid to be YOUrself! We are all unique, special and there’s only one AMAZING you!
Get the SQL Query used in DirectQuery mode
When you are optimizing your DirectQuery model and you have done all the optimizations on the model already, you might want to run the queries generated by Power BI by your DBA. He then might be able to do some index tuning or even suggest some model changes. But how do you capture them? There are a few simple ways that I will describe here.
1 Use DAX studio and Power BI desktop
We will start by opening the report and enabling the performance analyzer. This allows us to get each individual query base on duration so we could optimize them one by one. Click Start recording and refresh visuals to load the entire page and get all the queries send.
Once it has run you can copy the query. This is a DAX query so now we need to get the SQL query.
We can do this by running the query in DAX studio which will give us the SQL Query:
That’s option 1. This is a one by one approach.
1B Use DAX studio and Power BI desktop
I call this option 1B as you can change the flow a bit and just capture all the queries all at once in DAX studio too. Just turn on All Queries in DAX studio. This will show all the queries used for the page. For each query you can double click, execute it and get the SQL Query. Saves you copy and pasting but the result is the same.
2 Use SQL Profiler
Another option is to use good old SQL Profiler. You can connect this to Power BI desktop as well and get all the SQL queries in one swoop. Just connect to the diagnostic port of PBI desktop and capture all the “DirectQuery End” (or begin) event class.
This option is probably best if you want to get all the queries and give them to the DBA all at once.
3 Use log analytics
Now this one only works for Azure Analysis services at the moment but you can also capture the same trace events from profiler with Log Analytics as I described here. Just make sure you querythe same “DirectQuery End” event.
That’s it, several ways to get all the SQL queries generated by Power BI.
DAX Time intelligence and the 29th of February – #PowerBI
Yesterday I visited a client and was asked – how do the time intelligence functions handle the fact that February has 29 days in 2020.
Well – in fact there was a few surprises depending on what you select from you date table.
Let’s look as some examples – I will use the following Internet Sales Amount from the years 2011-2013 from Adventure Work Database where we in February 2012 have 29 days.
As you can see, we have the year 2012 where we have 29 days.
SAMEPERIODLASTYEAR()
In order to calculate Internet Sales Amount LY – I use the following
Internet Sales Amount LY = CALCULATE([Internet Sales Amount];SAMEPERIODLASTYEAR(DimDate[Date]))
Which works fine
But notice the behavior if we put dates or days numbers on the rows
SURPRISE – Internet Sales Amount LY will show the value for the 28th of February 2011 instead of a blank value as you perhaps would expect
If you select year 2013 we will see this
The 29 of feb 2012 will “disappear” but the total for February will include the number.
DATEADD() – last year
If we use the function DATEADD instead – it will work exactly the same way.
IAS LY = CALCULATE([Internet Sales Amount];DATEADD(DimDate[Date];-1;YEAR))
DATEADD() – same day last year
If you want to compare the same Saturday (the 29th of feb 2020 is a Saturday) last year – which is the 2nd of march we can do this by using the same DATEADD function but with different parameters
IAS LY same weekday = CALCULATE([Internet Sales Amount];DATEADD(DimDate[Date];-364;DAY))
This will compare the same day 52 weeks ago (52 * 7 = 364) and there by giving us the value from the 29th of feb 2012 on the 27th of feb 2013.
DATESMTD()
Now what about the function DATESMTD()
ISA MTD = CALCULATE([Internet Sales Amount];DATESMTD(DimDate[Date]))
ISA MTD LY = CALCULATE([Internet Sales Amount LY];DATESMTD(DimDate[Date]))
These functions will calculate the running total for the month for the given day number
Notice that the ISA MTD works fine in 2012 for the 29th and the LY measure will show the same result for the 28th and 29th in 2012 – and in 2013 it will actually for the 28th show the sum of both the 28 and 29th
Conclusion
You might find that some users find it difficult to understand how the calculations works when the look at dates instead of month totals especially in the case where they will get the same value for LY on both the 28 and 29th in 2012/2020.
If you compare cross years on calendar dates I find the result that SAMEPERIODLASTYEAR() returns makes better sense than leaving it empty/blank but what do you or your users think. Let me know in the comments.
Hope you find this little walkthrough useful.
And remember to ALWAYS use a datetable in your model if you do time intelligence calculations in DAX.
Show values in a chart only when a slicer is selected
Quick one today. This question came up recently: “I only want to show values in a chart when someone selected a slicer”. Now this is actually not that hard, so here is the trick (and a little bonus too).
First I created a page I wanted it including the slicer:
Now the next thing I want is that the values of the visual only show when I select a country. First thing I do is write a measure to only return the value of the measure when a country is selected through the slicer. I do this this by checking if there is a single value in the current context using the HASONEVALUE function. If there is a single value then show the measure value, otherwise return BLANK. When you return BLANK Power BI will not show the whole row or data point which is exactly what we want. BLANK just like in Excel means that it won’t be shown. The measure simply looks like this:
Measure =
IF ( HASONEVALUE ( Country[Country] ), SUM ( Sales[Units Sold] ), BLANK () )
Next I add this measure to the visual and voila we get what we want:
But is it? How confusing is it for users to just see empty whitespace? Probably very. So the first thing I would do is add a background to make it a bit more obvious something should be there.
But still this not really clear is it? What if we could give the users some instructions? Well turns out we can. Of course we can with DAX :). So we will do the reverse as before, when no slicer value has been selected I want to show some text. So I will create a measure and use a Card visual to show the text. The measure looks like this:
measure text =
IF ( HASONEVALUE ( Country[Country] ), “”, “Please select a country” )
I set the background to the card to 100% opacity and now we can tell the user to select a country first:
When I do select a country the label disappears (again using BLANK):
So that is pretty much it. Some pretty neat tricks that might be useful.
#PowerBI – Copy a measure in the desktop – Why it doesn’t always work – mystery solved
Well – creating measures in the Power BI desktop is usually doing a lot of copying and pasting of measures.
And unfortunately, we haven’t yet gotten a Copy measure in the shortcut menu when we right click a measure
Here is an idea you can vote for if you would find it useful as well – https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/13219620-duplicate-measure-and-format-copy
So we end up copying the formula from text in the formula bar
And click new measure and Paste it into the formula bar
But 8 of 10 times nothing is pasted (at least when I select) – WHY ???
And now I finally took me the time to find out why –
Notice the colour of the selection in the picture above compare to this
If the colour is darker blue you can copy and paste but if its lighter blue – you can’t – and this is properly because you use your mouse to select the formula and if you release the mouse outside of the formula bar area – the selection will be light blue and when you then press CTRL + C to copy the formula text isn’t copied to the clipboard.
Solution
So, either make sure you don’t move your pointer outside the area or use CTRL + A to select all and then press CTRL + C to copy the formula.
Hope this can save you a couple of clicks and please vote for the idea – https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/13219620-duplicate-measure-and-format-copy – this also includes the idea that the formatting of the value should be included.
Power On!
Archived MSDN and TechNet Blogs
MSDN and TechNet blog sites have been retired, and blog content has been migrated and archived here.
Archived blogs are grouped alphabetically by the initial letter of the blog name. Blogs and blog posts can be searched by their names, using the Search box at the top of the page.
Actively updated blogs have been moved to other blog sites, such as Microsoft Developer Blogs and Microsoft Tech Community Blogs.
If you had blogs hosted on MSDN or TechNet blog sites, and have questions or need help, please visit https://microsoft.sharepoint.com/teams/bloginfo/Articles/Home.aspx for more information.
All Blogs
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
U
V
W
X
Y
Z
0-9, Non-Alphabet Characters
#PowerBI – A quicker way to set Summarize to none for all your fields in your date dimension (or others)
When designing your model you will in most cases need a date dimension and one of the things you always should do is set the Summarization of the number columns in your table to none instead of the default Sum – to avoid this when you insert for instance Calendar year in your visuals
So set the summarization to Don’t summarize to avoid this.
But you have to do this for all the number columns in your date table – –
but if you use the Model view instead –
We can multi select columns and set it for all at once
In the model view we have to select None to avoid summarization of number fields – and voila all fields in my date table is set to don’t summarize
Hope this can help you as well.
Quick #PowerQuery trick – Get duration days between two dates
Just a quick tip that you might not be aware of in the Power Query Editor.
If you select two columns in the view and on the Add Column tab, select the Date button – you can select to Subtract Days
This will give you the number of days between the dates in the selected columns
Use the formula bar to rename the new column by modifying the step
Hope this can help you too –
5 reasons to use aggregations in Power BI
A lot has already been written on aggregations and it is one of the most exciting features of Power BI in a while. The guys at SQLBI did a session on it and my colleague Phil Seamark wrote an amazing host of articles with many details and tips and tricks on it. So why another article? Most people think aggregations is only applicable to petabyte scale data sets. That doesn’t have to be the true though, in this blog post I wanted to give my take on it and why it might be applicable to your current, smaller, model.
Here are 5 reasons you want to look at using aggregations:
- This is the obvious one. The data you want to analyze or report on doesn’t fit in memory. Imagine your users want to be able to see the details of every transaction and you have billions of rows. With aggregations you can store the aggregated values by product \ date etc in memory. You then leave the underlying transactions in the data source. However the magic here is that your users won’t even notice as it is one model and report.
- You want your data to be “real time” but not use DirectQuery all the time. When you are using DirectQuery for 100% of your queries are running against your data source. This might get you into performance and concurrency problems. With aggregations you can load a small set of aggregated data in memory that you can update and reload every minute or so. That aggregated data can take care of the majority of queries but will still be quick to load. You will have to be careful to make sure the data is in sync.
- Potentially you want to save cost. By determining what data is needed for 95% of the reporting cases you can trim down the data stored in memory. When data is loaded in memory it cost money when using Premium. Of course by offloading some queries to the data source you are adding load there too. So it depends on the ROI if this one is worth it.
- You want to optimize certain calculations. With aggregations you can use aggregation tables for DistinctCount calculations and use DirectQuery for lowest level calculations. Not just DisctinctCount will work but many other calculations too. This might be interesting to investigate but your mileage might vary. The team told me at one point we might have Aggregations over VertiPaq. When this happens performance optimizations using aggs will be even more interesting.
- Now the final one is to go read these blogs by Phil Seamark and try to understand what he is doing there. They show so many new ways This will allow you to solve many interesting scenario’s and allow you to”
I hope this has given you some idea’s for using aggregations even when you don’t have petabyts of scale.