Power BI: Show Visualization(s) Only When One Record is Selected

If you have some visualizations in your Power BI report that you only want to be visible when users have selected a single item from one or more filters, this blog post just might be the inspiration you need.

In my scenario I have a hierarchical series of slicers which allow users to narrow down the dataset to a single location. Only at the point where a single distinct location has been selected do I want additional visualizations to be visible on the page. These are mostly cards which are set to display the 'First' value in my dataset. Everything looks fine when I have just one location selected, but until that point there are multiple possible values and only displaying the first is confusing and just plain ugly.

I hope Power BI matures to the point of this sort of thing not requiring a weird workaround, but for now my odd solution is akin to a window shutter. I've added in a card over the visualizations in question that will alternate between being transparent or being filled with white to blend in with the background.

To accomplish this, I added a new measure to my dataset that counts how many distinct locations are being returned. If there is only one being returned then it will display an empty string (because this is when I will be setting it to be transparent). This is how mine looks, you might need to adjust it based on your own needs. In my case I was concerned with the distinct number of location codes specifically:

Locations Selected = IF(DISTINCTCOUNT('MyTable'[LocationCode])>1,DISTINCTCOUNT(MyTable'[LocationCode]),"")

Next I'm using an Advance Card visualization, because it's free and I like the added features that aren't included in what Microsoft gives you. Add in one of those and position it over the visualizations that you want to show/hide. Use the new measure you just created as the data field. Now in the 'Format' tab for this visualization, go to 'Conditions' and make sure it is set to 'On'. For 'No. of Conditions' enter 1. Then set 'If Value Is' to '>', 'Value' to '1', 'Then Foreground Color' to white (or whatever colour blends in with your background), and 'Then Background Color' to white as well. Make sure 'Background' is set to 'On' and that the colour is set appropriately.

Now when you run this report you will see this card either fill or be transparent based on how many distinct items have been selected according to your measure. It's weird but it works.

Read More

SSRS: Spaces in Column Headers in CSV Output

When you output an SSRS report to CSV format, the column headers in the resulting table are determined by the DataElementName of each textbox (this might sound confusing, just know that every cell in a table is actually a textbox). Editing this property lets you customize the output, but unfortunately there are some limitations.

The column headers must be CLS-compliant. Spaces unfortunately are non-compliant, so SSRS will automatically replace spaces with underscores for the DataElementName property. At the time of me posting this I'm not aware of any sort of clean workaround for this behaviour, but if something changes or if you have a method that has worked for you, please leave a comment below.

Read More

Power BI: Comments in DAX

You can insert comments into your DAX formulas in Power BI! Given how similar writing DAX in Power BI feels in comparison to entering a formula into Excel, I was very pleasantly surprised to learn that it's actually possible to write comments.

Start a comment with two forward slashes, and everything until the end of the line will be commented out. You can start a new line by pressing Alt + Enter.

Here are some keyboard shortcuts you can use to quickly comment or uncomment multiple lines in DAX:

• Ctrl + KC: Comment selected lines
• Ctrl + KU: Uncomment selected lines

Read More

Power BI: IS NOT BLANK

 In Power BI you can check to see if a particular value is not blank by combining the NOT operator with the ISBLANK function within your DAX expression. Here's an example:

= IF(NOT(ISBLANK('Table'[Column])),"Not Blank","Blank")

Read More

SSRS: How to Insert a Line Break in a Text Box Using an Expression

Inserting a line break into a regular text box is as easy as hitting the Enter key unless you're using an expression, in which case those line breaks are ignored.

If you are using an expression for a text box, concatenate with the following constant to insert a line break:

+ vbCrLf

This results in the insertion of non-printing carriage-return and linefeed characters (basically the equivalent of hitting the Enter key).

Read More

Power BI: IF Function Resulting in "Token Eof Expected" Error

Are you working on a very simple IF statement in Power Query, have gone over your syntax a thousand times, and are still seeing the following error message?

Token Eof expected.

Power Query is actually case sensitive. It's a shame it doesn't just tell you this fact as a side note to help you troubleshoot, because all you have to do is change your uppercase IF, THEN, and ELSE to lowercase if, then, and else.

I hope this post can save even one other person the maddening amount of time I spent trying to figure out what I'd done wrong in a very simple expression.

Read More

Power BI: Export to Excel or CSV

In a Power BI report if you have sufficient permissions (as well as a Pro or Premium license) then you will be able to export the underlying data for a visualization to Excel.

First, hover your mouse in the upper right-hand corner of the visualization until you see an ellipsis appear. If you put your mouse over it you will see the tooltip 'More options' appear. Click on this ellipsis and then click on 'Export data'.

Choose whether you would like to export the summarized data or the underlying data.

Choose a file format. Be aware that each format has a maximum number of records that it will export, so this won't work for larger datasets:

• Excel: 150,000 rows
• CSV: 30,000 rows

Click the Export button to finish.

Read More

Excel: Power View Not Visible on the Ribbon

Did you follow all the right steps to install Power View in Excel but still can't find it on the ribbon? You're not alone. After enabling Power View as a COM add-in, double-checking that it was enabled, and even restarting the application, I was still unable to find it anywhere on the ribbon.

There's a fix for this that will hopefully work for you the same way it did for me. I managed to launch Power View by manually adding it to the ribbon myself.

Right-click anywhere in your ribbon and choose 'Customize the Ribbon...'.

On the left-hand side of this window, in the 'Choose commands from:' drop-down, choose 'Commands Not in the Ribbon'. Scroll down the list and select 'Insert a Power View Report'.

On the right-hand side of this window, choose a tab and group where you would like to place your new button. I created mine in a new group called 'Power View' under the 'Insert' tab. Click the 'Add' button.

Now you should see a button labelled 'Power View' in the ribbon in the location you selected.

Read More

Power BI: Allow Export of Summarized and Underlying Data

If you're having trouble trying to export data from a Power BI report to Excel or CSV, a small tweak to the report itself might solve your problem. There is a setting that allows report authors to limit which data may be exported. Here's how to change that setting.

With your report open in Power BI Desktop, go to 'File', 'Options and settings', and then click on 'Options'.

In the left-hand side menu under 'CURRENT FILE' (not under 'GLOBAL'), click on 'Report settings'.

Now you should see a header called 'Export data'. In this section you can choose to allow end users to export summarized data, allow both summarized and underlying data, or to restrict data export altogether. Change this setting and republish your report.

Read More

SSRS: Remove Margins From Chart

 If you want to remove that extra white space from the sides of a chart in an SSRS paginated report, then you can do so by removing the margins.

With your report open in Visual Studio, select the chart axis and then open up the Properties pane. Scroll down to 'Margin' and set this property to 'False'.

Read More

SQL Server: End of Month Function

In Microsoft SQL Server there is a Transact-SQL function called EOMONTH that lets you find the last day of the month for a given date. You also have the option to include an offset value to add a number of months to the date that you pass in.

For example, if you want to get the last day of the current month, you can use:

EOMONTH(GETDATE())

If you want to get the last day of next month, you can pass in a second parameter of 1:

EOMONTH(GETDATE(),1)

As you might have already guessed, you can use a negative number if you want to subtract from the months. So if you're looking for the last day of the month from three months ago, you could enter:

EOMONTH(GETDATE(),-3)

For all of these examples you can swap in whatever date value you like in place of where I've used GETDATE().

Read More

How to Read Google Play eBooks on Kobo eReader

Since I get frequent discounts on eBooks through the Google Play Books store (details about how to do that here), I'm in the habit of buying my books there. My eReader however is a Kobo, which is set up to buy from the Indigo store. Here's how I load my books from the Google Play Books store onto my Kobo eReader.

1. Buy your eBook in the Google Play Books store.
2. Click the ellipsis next to the book title in your library and click 'Download EPUB'.
3. Download and install Adobe Digital Editions. This is free software that will let you load your EPUB file onto your Kobo eReader.
4. Open Adobe Digital Editions and go to 'File', 'Add to Library'. Select the EPUB file that you downloaded in the previous step.
5. Plug your Kobo eReader into your computer and wait for it to appear in Adobe Digital Editions. You should see it in the list on the left-hand side of the screen.
6. Click and drag the eBook from your library to your Kobo eReader.

That's it! 

Read More

Ergonomics: Working Comfortably From Home

Working from home is something very new to a lot of people who are suddenly shifting their workspaces in order to keep a safe distance from their coworkers during the COVID-19 pandemic. With considerable experience both working and studying from my home office, I thought I would dedicate a post today to my favourite tips for how you can work a desk job from home both safely and comfortably.

1. Make sure that your desk is set up in a way that is healthy for your body. The Canadian Centre for Occupational Health and Safety has some really great resources to help you do this properly, including how to position your monitor and how to adjust your office chair. You will significantly reduce your risk of repetitive strain injuries if you set up your space correctly.
2. Wherever possible, set a clear boundary between your work space and your living/recreation space. This is easier to do if you can dedicate a room as a home office, but it also applies to smaller homes. Resist the urge to work from your laptop while lounging in bed. It might seem luxurious but you don't want to blur the lines between a space intended for relaxation and a space meant for work.
3. In addition to setting boundaries for space, make sure you set boundaries for your time as well. The Canadian Psychological Association emphasizes the importance of respecting your work hours and preserving time for yourself and your family. Maintain a healthy balance of work and life and you will find it easier to be productive and happy with both.
4. Give your eyes regular breaks from staring at your computer monitor. The American Academy of Ophthalmology recommends following the "20-20-20 rule", which involves looking at an object at least 20 feet away for 20 seconds every 20 minutes.
5. Take coffee breaks the same way that you would when you are in the office. Set aside time to connect with your colleagues over instant messaging or phone calls to keep in touch. Not only is this good for your own personal well-being but it will help you to maintain your professional network which is so important for your career. I used to really dislike the awkwardness of small-talk in the office, but I recently learned that it serves a very important function. Check out this 2019 article from the New York Times that highlights to usefulness of socializing with your coworkers: The Awkward but Essential Art of Office Chitchat.
6. Don't eat lunch at your desk. Give yourself a proper break, eat somewhere else, and maybe go for a walk if the weather is nice. You'll feel refreshed and have a more productive afternoon.
7. Change out of your pyjamas, brush your hair, and wear something nice. I'll never understand people who feel the need to wear formal business attire in their home offices (unless they're on a lot of important video calls, of course), but you will feel better if you get dressed for the day - even if you have no intention of stepping foot outside or seeing another living person.
8. Get help if you need it. Working from home can be a really drastic change for some people, and it's important to keep an eye on your mental health. From my own personal experience I highly recommend cognitive therapy.

Read More

SQL Server: Sorting by Column Numbers

If you've come across this post then you are likely aware that in SQL Server you can use the ORDER BY clause to sort by specific columns by name, but did you know that you can also do it by number? It's a quick and dirty way to sort without having to spell out each column name.

Here's an example of how you could sort by the LAST_NAME column:

   SELECT USER_ID,

          FIRST_NAME,

          LAST_NAME

     FROM MY_TABLE

 ORDER BY LAST_NAME

Or, you can accomplish this same thing by using '3' in the ORDER BY clause. This tells SQL Server that you want to sort by the third column (which is LAST_NAME):

   SELECT USER_ID,

          FIRST_NAME,

          LAST_NAME

     FROM MY_TABLE

 ORDER BY 3

As with everything in tech you'll find varying opinions on whether or not this is a good thing to do. I would say that it's worth using if you're just writing a quick ad-hoc query. It can save time. On the other hand, if this is something you plan on saving or reusing, I would advise entering the column name instead. This way you know it will keep sorting the way you intended even if you add other columns later on that end up changing the position of the column by which you wanted to sort.

Read More

Power BI: Subreports

Great news for Microsoft Power BI users: they've just released an update that allows for the creation of subreports! This lets you to embed one report inside of another, something that can get fancy if you start passing parameters from the main report to the subreport.

In order to use this new functionality you will need to download the latest version of Power BI Report Builder.

Read More

How to Get Discounted or Free eBooks (Legally!)

I stumbled upon the perfect duo: Google Opinion Rewards and Google Play Books. Earn Google Play credit using Google Opinion Rewards and then spend the credit in the Google Play Books store to get a deal on any eBooks you want to buy.

1. Download the Google Opinion Rewards app on your smartphone. Use it to answer very short surveys in exchange for Google Play credit. I really do mean short, by the way. Typically I will spend about 15 to 30 seconds on a single survey and each one can be worth up to $1.00.
2. Spend your credit in the Google Play Books store!

Lately I've been reading faster than I can earn credits, but I'm still getting discounts on every book I buy. It's a very low effort way to get discounts on eBooks while still purchasing them legally.

Of course you can use your Google Play credit for anything in the Google Play store, I just happen to be interested in using it for books specifically.

Read More

SSRS: Colour Striping Rows

Tabular reports are so much easier to read when you fill in alternating rows with different colours. This becomes especially important when you have particularly wide tables. This technique is also called zebra stripes or candy stripes. Cute.

In Visual Studio, select the row of your tablix data region (table) and enter the following expression for the 'BackgroundColor' property (under 'Fill'):

=IIF(RowNumber(Nothing) Mod 2, "Black", "White")

Replace the colours there with whichever colours you want to use. In this very unlikely example I have it set to stripe the rows in black and white. You can enter simple text colour names like I did, or you can enter hex codes to get the exact colour you're wanting.

One thing I really like doing in my reports is to set up my own colour palette using report variables. I will set variable named like 'AccentColour1' and set the value to whatever hex code I want (e.g., #87CEEB). Then when I am doing striping like this and want the colours to be consistent with the rest of my report, I will enter the variables into the expressions instead of hard-coding the colour names or values. Here's what that would look like:

=IIF(RowNumber(Nothing) Mod 2, Variables!AccentColour1.Value, Variables!AccentColour2.Value)

I'm a fan of this approach because you can easily switch up colours across your entire report if needed instead of having to go in and edit them for each object individually.

If you're looking for my post on how to do this in Excel, check out that post here.

Read More

SSRS: Collapse Parameters Pane by Default Using URL Parameters

If you have an SSRS report deployed to a Report Server you can use URL parameters to link to it in such a way that the parameters pane is collapsed by default. This can be helpful especially when your clients aren't likely to use it very frequently and if it takes up a lot of space on the screen.

All you have to do is add on the following to the end of your report URL:

?rc:Parameters=Collapsed

So it will look something like this:

https://server/reports/report/FolderName/ReportName?rc:Parameters=Collapsed

It's that easy!

Read More

Windows 10: Emoji Keyboard

I came across this keyboard shortcut entirely by accident while trying to lock my computer, and of course I had to share it here. Did you know that there's an emoji keyboard in Windows 10 and you can open it by pressing the Windows key and the period button? Too cute!

Read More