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

Power BI: Totals Not Working? Mind Your Cross Filter Directions

If your totals or other measures for related tables don't seem to be working properly in your Power BI report, it might have something to do with the cross filter direction(s) in your data model. Here's what my situation was and how I fixed it:

I had a simple table visualization that was supposed to be pulling in various summary measures from another related table. Some of these were just basic totals. Now when I applied filters to this table, for some reason the totals from the related tables weren't also recognizing these filters. It was just giving me a total for the entire related table instead of also taking into account the filter. I'm pretty new to DAX and went in circles agonizing over whether I had even created those measures properly in the first place.

My problem had a quick solution: modify the cross filter directions for the relationships between the affected tables. Look at the relationship lines in your data model and you will see a little arrow on them. By default this will just show one arrow, which means it is set to a unidirectional cross filter. Double-click this relationship line to edit it, and in the 'Cross filter direction' drop-down box, select 'Both'. Now you will see two arrows on the relationship line instead of just the one. And now you should notice filters being applied properly in both directions, if this is what you needed.

To avoid this problem in the future, make a habit of intentionally setting your cross filter directions when you create relationships in Power BI.
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