SSAS: Count Records in Table

The DAX function to count the number of rows in an SSAS table is COUNTROWS. Here's an example of how you could use it if you had a table called 'Products':

=COUNTROWS('Products')
Read More

Power BI: Lock Layered Objects

If you've created a Power BI report with layered objects and don't want background elements to jump to the front when they're selected, thankfully there is an easy solution.

Select the element you want to lock in place, such as a rectangle, and go to the Format tab on the Visualizations pane. Under the 'General' group, turn on the 'Maintain layer order' property. When this setting is enabled, your visualization will stay locked in place instead of moving to the front when it is selected.

Read More

SSRS: Tables in Headers and Footers

I'm going to make this post short and sweet and cut to the chase: no, you cannot insert a tablix table into either the header or footer of an SSRS paginated report.

Sad face.

Read More

SSAS: Objects Randomly Disappearing

I don't have a fix or explanation for this but since I had a hard time finding anyone else with a similar experience online, I figured I should make a post about it. Have you ever tried adding something new to your SSAS model such as a table or relationship, and found it seemingly never appearing? You know for sure that you followed the right steps to create something but you can't find any trace of it in the interface? You're not alone. This happens to me quite frequently and has had me question my sanity until I saw it happen to a colleague too.

If things seem to be randomly disappearing in SSAS, just close and reopen your project in Visual Studio and suddenly you will find them right where you expected them to be.

I'd love a comment from anyone who can explain this or tell me if there's a fix!

Read More

Neopets Links

What can I say? It's 2021, my Neopets account is 20 years old, and I'm still playing. I made this page of links to use because their navigation is a little wonky lately. Leaving it public in case it's useful for anyone else. There are more extensive pages like this out there, this is just the subset of items that I'm most interested in.

Daily

Periodically

Read More

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