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