Combine Datazen dashboards with Power BI Q&A functionality

Happy Friday, everyone!

Did you know that Power BI now allows you to directly link to individual dashboards?  This got me thinking – I love the Q&A functionality in Power BI.  Could I easily put together a solution where I used Datazen for my dashboards and jumped into the Power BI service to ask additional questions?  Why yes, yes I can.  Here’s how –

For this example, we’ll use a simple Excel workbook as the data source.  But let’s leverage the capabilities of OneDrive for Business to make this solution a bit more elegant.

Why?  Couple reasons –

1. Power BI will automatically refresh datasets sitting in OneDrive every hour, so anytime you update your Excel data, it’ll get grabbed automatically by Power BI shortly thereafter.

2. You can leverage the OneDrive for Business sync client to save Excel files locally on your PC and have them automatically sync to other machines running the sync client.  This makes it easy to get my Excel files onto my Datazen server after I’ve installed my sync client on there – I can refresh my files locally, save them to my OneDrive folder on my PC, and they’ll show up on the Datazen server within minutes!  I could even use a tool like Power Update to automate the data refresh of my Excel files.

Let’s get started –

First, I’m going to open Datazen and draw my dashboard first so I know how I should lay out the data in my Excel workbook.

With that done, I’m going to create my workbook from my live data source using Power Query to make sure it’s laid out the way I need.

Now, I’m going to save the file to OneDrive for Business.  Once that’s done, I’m going to my PowerBI.com site and creating a new dashboard called “StoreQA”.

Now, I’m going to add my data to the dashboard.  So I click “Get Data”

Then I select “Files”

Now I want to select the “OneDrive for Business” tile

And then find the file I just saved

and hit “Connect”.  It’ll then bring my data into the dashboard and I can leverage Q&A with it.

I’m going to leave this open for now, and jump back to my Datazen Publisher App to bring in the Excel data to my Datazen dashboard.  You’ll need to setup the OneDrive for Business folder on your Datazen server as the location for the Excel data (this assumes you setup the sync agent on the box already).

With that done, you can bring in the Excel data to your Datazen dashboard

 
and hook back in the elements accordingly.

Once that is done, you’ll want to create a link from the Datazen dashboard to the Q&A in Power BI for this dataset.  Go back to your browser and grab the URL for the dashboard from the address bar.  It’ll look like the example here – https://app.powerbi.com/dashboards/xxxx

To go directly to the Q&A functionality for this dashboard, just add ‘/qna’ at the end of the url, so it then looks like this – https://app.powerbi.com/dashboards/xxxx/qna
You can test it out and see how it takes you right to the question and answer area for this dashboard –

Simply copy that link and select an element on your dashboard you’d like to link to the Q&A piece from

by using the Drill-Through Target functionality to point to a custom URL

Now when I click on the element, I get right to the Q&A page in Power BI with the same dataset so I can ask it more questions about my data

If you really wanted to get clever, you could even use the parameter functionality.  See how when I ask a question in Power BI, the URL changes to include the question text –

https://app.powerbi.com/dashboards/f31f7d2f-13ba-4e21-8b22-754ac62d39d7/qna?q=what%20were%20total%20sales%20for%20grocery%20by%20year

and gives me the following result

I can simply change it to use a parameter instead, so it would look like this –

https://app.powerbi.com/dashboards/f31f7d2f-13ba-4e21-8b22-754ac62d39d7/qna?q=what%20were%20total%20sales%20for%20{{ SelectionList01.SelectedItem }}%20by%20year

and when I run my dashboard, I can change the dropdown

and the question will dynamically change when I click on my element with the link to Power BI!

Remember, you have to publish the dashboard to your server before you can test the URL drillthrough in Datazen!

And with Power BI now having the ability to setup custom links with it’s elements, you could even pin the tile for the question you just asked so you could jump back to your Datazen dashboard!

 
Hopefully this helped open up a number of possibilities with the tools for you to explore.  Have a great weekend, and don’t forget to download the Windows 7 publisher app currently in preview from here and give it a spin.

Thanks for reading!