Power BI – How to clear all slicers

jlsqlLeave a comment

ClearAllBookMark

I was delivering my standard Power BI training course, and there was a question from one of the users which was ‘Is there a way of clearing all the slicers you have selected, so if you are doing something you can go back to the start’, my answer was ‘No… wait, yes… you could use a bookmark to do it’

So how to clear all slicers? First of all in Power BI Desktop you need to go to view and add the Bookmarks Pane. Then make sure that all the slicers are clear, or in the default state that you want them. In this case I made sure that everything was deselected. Once that is done, Click ‘Add’ on the bookmarks pane, that will create the Bookmark, I’ve renamed mine to ‘Default’

BookmarksPane

Next, add a button to your report, and allocate the ‘Default’ Bookmark to the Action setting. I’ve selected the ‘Reset’ button and added the text ‘Click to reset’ on mine. Next select some items, once you are done and want to go back to the start, click on the button (Ctrl-Click in Power BI Desktop), that will apply the default Bookmark, and in my case clear all the slicers! Nice quick and easy solution.

 

 

 

 

Your Brain & Report Design Part 2 – Reports and Cognitive Burden

jlsqlLeave a comment

Not done a blog post for a few months, sorry work getting in the way a bit.

In Part One I talked about the Intuitive (System 1) and the Attentive (System 2) processes, and the separation and focus of these mental processes. Here is a quick reminder of the functions of both parts.

System 1 (Intuitive) System 2 (Attentive)
Fast Slow
Unconscious Conscious
Automatic Effortful
Everyday Decisions Complex Decisions
Error Prone Reliable

Cognitive Burden

In an number of texts about report design, the phase Cognitive Burden is used. With the outline of System 1 and 2, cognitive burden is impact of switching between the two functions and making it harder for the System 2 Attentive process to work out what is going on. So in some regards you can use bits of System 1 to help with System 2, and also focus System 2 get to the underlying understanding of the information a bit quicker.

The main outline of this approach is that of simplification… reduce distraction, reduce clutter, remove anything that is not needed, and this is done in a number of ways:

  • Layout
  • Ordering
  • Colours
  • Visualisation Selection

Also to consider will be ‘Framing the Answer’, how best to present a value, with less abstraction of the how you view it, (which sounds trickier than it is) which I’ll cover  in another post. So in this post I’ll be covering the first two, Layout and Ordering.

Layout – Arranging the space

How to leverage System 1 in layout of reports? Well you use some thing that is easy, intuitive and you are doing it right now… reading. The process of reading is from left to right, top to bottom. However this is approach is biased to the English language in my case, so if you are creating reports in Japanese, you’ll be doing it right to left.

When designing reports, I don’t tend to add the logo of the company that it is for. This is purely a personal approach, I don’t see the need to remind the reader of the company that they work for and it also takes up space on the report, that could be best served for information. So if I do have to add a logo or other graphics or even a title, I tend to put them in the top right. Some times if I can get away with it, the logo goes down the bottom right, nicely out of the way.

So in layout terms I would place what you want the viewer to see first on the left side or in the top left, so they get that first information hit, if it is a particularity important value or report that you want them to see first.

Stop and Search - Power BI

In the above case the three card visuals are the first thing I want the viewer to see, then the Map to supply context, then the rest of the charts.

The next technique I use is to layer the report into three sections based on the granularity of the data, so cards would be at the top, high level data, then charts, then table data.

Stop and Search 2 - Power BI

So as the user moves down the report there is a reveal of information, and you can see the break down of the details as you move though the report.

So, in the above reports, you have used the learned System 1 process of reading and used it on a report. Now you can layout a report, next I’ll be talking about the importance of selecting the right visual.

 

Your Brain & Report Design Part 1 – The Attentive vs the Intuitive Process

jlsql2 Comments

image11I do a number of Power BI workshops, were over a few days I guide people through the features of Power BI, what it is good at, what its limitations are, and after a training exercise on a basic data set to familiarise the clients with Power BI Desktop, we get started on report building with their own data. In that phase of the workshop, it is always interesting to see what they build, particularly how they lay it out and what colours they use.

After some, lets say, very interesting design choices, I started adding in a section into the workshop slides on report design best practice, mainly based on the guidance in the book ‘Information Dashboard Design by Stephen Few‘ and ‘Storytelling with Data by Cole Knaflic‘. In these books, and a few others, it talks about the ‘attentive process’, and how by incorrectly choosing visuals you make the information presented hidden, obscured or you just can’t see the pattern.Read More »

Power Query & finding the Nth instance of a letter in a string for manipulation

jlsqlLeave a comment

Power BI LogoPower Query and the M language is a bit of a pain, not a lot of people do it (Or they do, and they don’t write about it), and the knowledge base on MSDN is not real world example friendly. So trying to figure out how to do something you know how to do in say SQL or DAX is not that easy some times. Where possible it is best to do as much as you can in the Power Query loading, otherwise you’ll be burning CPU time in the data model. So things like data cleansing should be done in Power Query, not in the DAX after it has been loaded into memory.

So find the Nth instance of a item in string I can do in SQL, but in Power Query it is a little bit different. So lets start with an example that contains the following.

ABC-01-02-03-XYZ

So I had to remove the ‘XYZ’ bit. Easy right?


Text.Start("ABC-01-02-03-XYZ", 12)

No, turns out the data contain items like

AB-012-02-03-XYZ

ABC-01-02-09

AB-012-032-03-XGYZ

So no quick fix by just using basic string manipulation. So what can we use? In this case you can use the Text.PositionOfAny function, and with this you can set it to find the first, last or all occurrences. So in this case, looking for the first instance of ‘-‘, you would get:

 


Text.PositionOfAny("ABC-01-02-03-XYZ", {"-"}, Occurrence.First)

Would return 3. Wait, not 4? No as it starts from ‘0’. It’s like a JSON array or Python dictionary index. This will be important later.


Text.PositionOfAny("ABC-01-02-03-XYZ", {"-"}, Occurrence.Last)

Returns 12.


Text.PositionOfAny("ABC-01-02-03-XYZ", {"-"}, Occurrence.Any)

Would in a custom column, just return ‘List’, or internally {‘3’, ‘6’, ‘9’, ’12’}. You can expand the list out if needed and create extra rows, but you should not in this case.

So to strip out the ‘XYZ’ part of ‘ABC-01-02-03-XYZ’

So you can use Text.Start, so it will read from the start of the string to the end, which can be set from a list.


Text.Start("ABC-01-02-03-XYZ", (Text.PositionOfAny("ABC-01-02-03-XYZ" , {"-"}, Occurrence.All){3}))

So in the above code the {3} is get the value from list in that location. Remember lists start from 0, and {3} would return the value 12 in the list {‘3’, ‘6’, ‘9’, ’12’}

But for the other instances in the data, {3} might not exist. However you can problematically strip stuff out, with for example an ‘if’ clause, with List.Count and drive the extraction of the data, based in the number of items in the list.

So for example in the data I have in the column [SortID] the following

AB-012-02-03-XYZ

ABC-01-02-09

if List.Count(Text.PositionOfAny([SortID] , {"-"}, Occurrence.All)) <= 3
then [SortID]
else Text.Start([SortID], (Text.PositionOfAny([SortID] , {"-"}, Occurrence.All){3}))

So in the 'if' clause List.Count on ABC-01-02-09 will return 3, and then do nothing, as I don't need to trim the end off it. Otherwise it will take AB-012-02-03-XYZ and find the position of the 3rd list value, and take the string length from it found in the Text.PositionOfAny, and trim the end off.

If you want here is some example M code to recreate the above. Enjoy

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRy1jUw1DUw0jUw1o2IjFKK1UERtFSKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [SortID = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"SortID", type text}}),
#"Basic String Manipulation" = Table.AddColumn(#"Changed Type", "Basic String Manipulation", each Text.Start([SortID], 12)),
#"First Occurrance" = Table.AddColumn(#"Basic String Manipulation", "First Occurrance", each Text.PositionOfAny([SortID], {"-"}, Occurrence.First)),
#"Last Occurrance" = Table.AddColumn(#"First Occurrance", "Last Occurrance", each Text.PositionOfAny([SortID], {"-"}, Occurrence.Last)),
#"All Occurance" = Table.AddColumn(#"Last Occurrance", "All Occurrance", each Text.PositionOfAny([SortID], {"-"}, Occurrence.All)),
#"If Clause" = Table.AddColumn(#"All Occurance", "If Clause", each if List.Count(Text.PositionOfAny([SortID] , {"-"},
Occurrence.All)) <= 3
then [SortID]
else Text.Start([SortID], (Text.PositionOfAny([SortID] , {"-"}, Occurrence.All){3})))
in
#"If Clause"

 



 


 


 

		

	
	

		

					

	



			
				


		

		
Power Query – M Calendar Function
					

				 jlsqlLeave a comment			

			


			

			
Power BI LogoI’ve downloaded a few M language based calendars, for use in Power BI recently and have been frustrated as they don’t quite meet my needs. So I’ve created my own.


The main difference to some I’ve seen online is that the time category columns, Month, Quarter, Year etc. columns are only dependent on the date column, nothing is dependant on anything else, so if you don;t need them you can delete them. The other option, is the use of a key column for sorting. So in Power BI you can use the sort by other column, and stop the month names being sorted alphabetically in charts/slicers and so on. You can define the start if needed and creates the calendar to the current now date.


Also I now have a GitHub!


Code is here enjoy


Or copy and paste from belowRead More »

		

	
	

		

					

	



			
				


		

		
SQL Server 2017 – TRIM, at last!
					

				 jlsqlLeave a comment			

			


			

			
AzureSQLLogoThere is loads of good stuff in SQL Server 2017, run on Linux, Graph Databases, Python, but some times it is the small things in the daily T-SQL grind that really make the difference. The Trim function is one of these.


Cleaning up dirty data from source systems, well I say source systems, most likey xls, xlsx, csv and tsv files, your going to come across leading or trailing spaces, the ghosts in the string!


So for example if we had ‘   Some string from some funky csv export   ‘ with 3 leading and 3 trailing spaces we had to do this in SQL Server 2016 and below:


DECLARE @str AS VARCHAR(50)
SET @str = '  Some string from some funky csv export   '

SELECT RTRIM(LTRIM(@str)) AS TrimExample



Which can be converted to a UDF so you could just do your own TRIM function, but it would look untidy with a schema in front of it, blah!


DECLARE @str AS VARCHAR(50)
SET @str = ' Some string from some funky csv export '
SELECT udf.TRIM(@str) AS TrimExample



But now you can just do


DECLARE @str AS VARCHAR(50)
SET @str = ' Some string from some funky csv export '
SELECT TRIM(@str) AS TrimExample



No more having to recall which is your left and right, nice and tidy! Worth updating just for this function IMHO.

		

	
	

		

					

	



			
				


		

		
Power BI Custom Visuals – My top picks part two
					

				 jlsqlLeave a comment			

			


			

			
If you missed part one of ‘Power BI Custom Visuals – my top picks’, which was basically a love letter to the OK Viz team, check it out now.


So this is the second part of my top picks, looking at the visuals I tend to use when creating Power BI reports. As mentioned in part one, there are about 12 ones that I use, I do tend to be fairly conservative in using custom visuals, there are a quite a number now, and tend to side with functional, and less flashy/gimmicky choices, as those tend to  kick in the attentive process and not be as intuitive to use, and ultimately distract from the information that you wish to show. Anyway here’s the final few.Read More »

		

	
	

		

					

	



			
				


		

		
SUMX vs DISTINCT COUNT
					

				 jlsql2 Comments			

			


			

			
During the preparation for delivering a Power BI training session for a client, I was looking at the Tabular Data Model that was their data source, and I was struggling with a long running query. It was taking 1 minute and 24 seconds to return the values after chewing through 2 million rows of data. So I had a go at optimising it… and got it down to 6 seconds.


Mind Blown


Wow! When the query ran in that short of time, I went ‘No way… that’s not right, what have I done wrong?’, but no, after checking it was right.


Let’s start at the beginning…Read More »

		

	
	

		

					

	



			
				


		

		
Power BI Embedded, SKU Differences and Cost Breakdowns
					

				 jlsql3 Comments			

			


			

			
Updated: 2021/04/20 – Updated the EM SKU’s to clarify what items you can consume


There still seems to be a bit of confusion about Power BI licensing since the changes made back in May 2017. I get a number of questions about embedding options, were a client wants to share reports internally via SharePoint and/or externally. Now if a customer is a large enterprise and wants to share internally via SharePoint, the best option is Power BI Premium. In Premium you can allocate Power BI work spaces to capacity, so those who log into Power BI with a ‘Free’ license or use SharePoint can see the reports they need. However for smaller organisations paying £3,766 per month is not going to be viable. There are some other options, which is were the confusion creeps in. I’ll be using a very raw measure of Monthly Cost to Power BI Pro Licenses, to show how to get the best bang for your buck/quid/currency denomination of your choice.Read More »

		

	
	

		

					

	



			
				


		

		
Power BI Custom Visuals – My top picks part one
					

				 jlsql2 Comments			

			


			

			
Recently I presented a talk at the SQL Server Birmingham User Group, chatting about how you interpret information and some of the best practice methods of presenting your reports. I had few questions asked at the end of the session, but got stuck on one, which was ‘Which Power BI Custom Visuals do you use and recommend?’.  I was stuck for a moment and named a one or two, but I do use a few more, so here are my top picks from the Office Store.


Please note, the following list is in not any order of use, but it does seem to be split between OK Viz visuals and the rest. So out of 12 I use the most, 5 are from them, which is not surprising as they are awesome. In fact in adding the links to this post, they have released a new one, the Colour Helper. As I am super duper colour blind, it is very helpful!Read More »