Tag: Data Visualization

Business Intelligence - BI, Power BI, Power BI Desktop, Quick Tips

Quick Tips: Hiding Multiple Columns in Power BI Desktop

From now on I will post some quick tips to help you accelerate your Power BI Desktop development. As the first post of these series, I explain a simple way for hiding multiple columns in Power BI Desktop. To do so:

  • Navigate to Relationships veiew in Power BI Desktop
  • Maximise the table you want to hide some columns in
  • Select the first column
  • Pres Shift and use arrow keys to highlight multiple columns
  • Here is the trick: DO NOT RIGHT CLICK! Instead, press the menu button on your keyboard. If your keyboard doesn’t have contect menu button, don’t worry, you can press Shift + F10 to do the same job

Menu key on PC Keyboard

  • Then click on “Hide in report view”

Enjoy!

Select Multiple Columns in Power BI Desktop

Business Intelligence - BI, DAX, Power BI, Power BI Desktop, Power BI Documenter, Power Query

What is Power BI Documenter

Power BI Documenter Logo (Small)

On Saturday, 9th June 2018, we announced the existence of Power BI Documenter. As the name resembles, Power BI Documenter is a tool to help individuals and businesses to document their Power BI Desktop models. Everyone who already have several Power BI Desktop reports probably realized that documenting the solutions is not as easy as how creating a report in Power BI Desktop is. The issue is more visible in larger organisations with several Power BI Developers who are busy enough with a big list of tasks that are assigned to them on a day to day basis. Therefore, there is no time left to take care of the documentation. Every IT expert knows how important is to have proper documentation. We at Data Vizioner decided to do something tangible about this issue. So we started the project several months ago with the vision of creating web app to help individuals and businesses to keep their Power BI documentation on track. In this post I’m not going to explain how you can easily start documenting your Power BI Desktop reports using Power BI Documenter. You can learn more about Power BI Documenter and how to use it here. Despite the fact that the current version of Power BI Documenter is the very first version of the app with lots of limitations, it indeed can help users with their Power BI documentation tasks. All you need to do is to export the Power BI Desktop files (PBIX) to Power BI Template format (PBIT) and upload it to Power BI Documenter web app. Continue reading “What is Power BI Documenter” →

BISM Tabular, Business Intelligence - BI, Power BI, Power BI Desktop, Power Query, SQL Server Analysis Services - SSAS

DAX Measure Dependencies in SSAS Tabular and Power BI

DAX measures are the heart of every SSAS Tabular model, Power BI and Power Pivot solution. You write lots of DAX measures and you potentially reference some of them in other measures. So the number of DAX measures you write and reference them via other measures grow very quickly. Especially in complex solutions you may have hundreds of DAX measures. While your solution works perfectly, to make a minor change or adding a new measure to the solution or fixing a problem in your existing measures can be such a pain in the neck. In this post I’m going to take a step further and show you a simple way to get the whole data model dependencies then visualise the dependencies in Power BI. You can find the download link at the end of this post.

A simple search in Google brings you a bunch of useful articles talking about the subject. Some of the bests, in my mind, are as below:

In this post I use a DMV that gives us everything we want. ( Chris Webb already discussed the DMV here: Document Dependencies Between DAX Calculations). Running the DMV we can see what measures are references by other measures, what columns are referenced in the calculated columns and much more.

This is a very useful DMV that helps us getting a better understanding of the model we’re working on. We can also use this method for documentation.

How It Works

This method is fairly simple, you just need to run the following DMV on top of your SSAS Tabular model or your Power BI Desktop file and Import the results in Power BI.

SELECT * FROM $System.DISCOVER_CALC_DEPENDENCY

For Power BI you’ll need to find the local port number then you’re good to go. The only part that might not look very straightforward at first, would be finding the database in Power BI Desktop model.

An easy way, after you find the local port number of an opened Power BI Desktop file, is to find the database name from SQL Server Management Studio (SSMS) when connecting to the Power BI Desktop model:

  • Open SSMS
  • Select “Analysis Services” as “Server Type”
  • Type in “localhost:PORT_NUMBER” as “Server Name” then click “Connect”

Connect to Power BI Desktop Model from SSMS

Continue reading “DAX Measure Dependencies in SSAS Tabular and Power BI” →

DAX, Power BI, Power BI Desktop, Power BI Service, Power Query

Analyse Your WordPress Website Stats in Power BI

WordPress is one of the most popular open-source website making tools which is vastly used by many bloggers including myself. WordPress developers provide tons of custom plugins to fulfil different needs. But not all of the plugins are well designed and secured.  One of the most interesting pieces of information anyone who owns a website/blog needs is their website statistics. In this post, I explain how to analyse your WordPress website stats in Power BI. Before I start, I want to point out that there is a WordPress stat already available in WordPress Admin Dashboard which provides very informative information about your blog like Total Views, Today Views, Best Ever Views and so forth. You can also install the WordPress app on your mobile device to easily access your website stats. But, the stats WordPress gives me in not enough. I want more. I need a more detailed analysis on

  • Current Month vs. Last Month
  • Current Year Vs. Last Year
  • Most Popular Day of Week
  • Most Popular Month of Year
  • Top 10 Posts

and so on.

As non of the above analysis are available in the normal stats , I decided to build my own version of “WordPress Website Stats Analysis in Power BI”. This gives me the flexibility of creating as much analysis as I need , and… it is so much fun.

If you own a WordPress blog or any other sort of websites or if you’re just curious to learn how to use a website API in Power BI, then this post is for you.

I managed to create a Power BI Desktop template that you can download and use it for free. You’re welcome to modify it based on your needs. You can find the download link at the button of the page.

How It Works

To be able to analyse your WordPress stats in Power BI you need to own a WordPress blog or website. Then a WordPress API key is assigned to your account. The key was included in your WordPress Welcome Email. You can use that API key in Power BI Desktop to create your customised reports and analyse your blog/website stats then you can publish the model into Power BI Service that is accessible anytime anywhere. You can also create your own dashboard in Power BI Service.  Moreover, you can setup Schedule Refresh for the dataset to refresh your dashboards and reports automatically.

Requirements

You’ll need to

  • own a WordPress blog/website and have the API assigned to your account
  • install the Jetpack plugin in your blog/website as WordPress stats is no longer maintained and you should not use it. Instead, you can install the Jetpack plugin
  • have Power BI Desktop installed on your machine (Download it from here, it’s free!)
  • have a Power BI account (Don’t have an account? Signup for it here, it’s free too!)
Continue reading “Analyse Your WordPress Website Stats in Power BI” →