Blog – Page 36 – Prologika

Applied Power BI Book (3rd Edition)

December 31, 2017 / No Comments »

I'm excited to announce the third edition of my Applied Microsoft Power BI book! When the original book was published in January 2016, it was the first Power BI book at that time. Since then, I helped many companies adopt or transition to Power BI, and taught hundreds of students. It's been a great experience to witness the momentum surrounding Power BI. The third revision added more than 20% new content (the book is now 426 pages) and probably that much content was rewritten to keep the book up with the ever-changing world of Power BI. Because I had to draw a line somewhere, Applied Microsoft Power BI (3nd edition) covers all features that were that were released by early December 2017. As with my previous books, I'm committed to help my readers with book-related questions and welcome all feedback on the book discussion forum on the book page. While...
Field Description Support in Power BI Desktop

December 11, 2017 / No Comments »

I'm a big fan of self-documented semantic models. Business users have a hard time getting their way around complex models and descriptions can go a long explaining the purpose of metadata. After waiting for years for Excel to support field descriptions (the wait is still on), the December update of Power BI Desktop now supports them. The new Field Properties window lets you enter descriptions for self-service data models. When connecting to Analysis Services, descriptions just light up on hover. A true Christmas gift! And Q&A in Power BI Desktop makes it ever better.
Power BI vs. Tableau (Part 3)

November 10, 2017 / No Comments »

Feeling the heat, Tableau has come up with an updated Power BI vs. Tableau battle card as one of my clients pointed out. This time designed as a video. I guess the previous "10 Ways Power BI Falls Short" slide deck, which I discussed in the part 1 and part 2 of my "Power BI vs. Tableau" blog , wasn't effective enough. I concur given the large number of customers abandoning the tool. Tableau is desperately trying to breathe new life into their aging software by a series of acquisitions to stay competitive but they're now fighting an uphill battle. And their marketing materials should have a timestamp because Power BI improves every month and points get outdated quickly (see the first part 1 of my blog). But let's take a look at the latest battle card so we are better positioned to answer the question asked by Tableau "Which one...
Hey Cortana, where is Power BI data?

November 6, 2017 / No Comments »

A Power BI presentation is rarely complete if someone doesn't ask me to demonstrate the Cortana integration. However, unlike everything else in Power BI that gains features in time, in my opinion the Cortana experience has lost some value. Previously, you could ask natural questions in Cortana across datasets, just like you can use the Power BI Service Q&A to gain insights from dashboards. Unfortunately, Microsoft has removed this feature in favor of searching for report pages that are specifically optimized for Cortana (also known as Cortana answer cards). You can also search dashboards and reports by name but that won't get you the "vow" effect since 1) you need to know the name and 2) when you click the name Cortana opens Power BI in a separate browser window. So much about natural queries and machine learning. I think the change took place after Microsoft decided to move away...
Power BI Service in Get Data

November 5, 2017 / 1 Comment »

Scenario: You have published a Power BI Desktop file with a report to Power BI Service. You want to give certain users access to some pages in the report. This is not a security mechanism although it could be, if you want to control security at the report layer. Solution: When a customer asked about this scenario, my first thought was to create reports directly in Power BI Service. For example, the report that's included in the Power BI Desktop file could have some visuals that you might want to share with everyone. Then, you create additional reports for each group of users and share them via dashboard sharing. This approach will work but if someone nukes the dataset, your reports go down with it. True, you can export a report connected to a dataset created in Power BI Desktop but who bothers to back up reports on a regular basis?...
Power BI DirectQuery with Parameterized Stored Procedure

October 31, 2017 / No Comments »

Issue: You have a parameterized SQL Stored procedure that you want to call in DirectQuery mode. You attempt to use "exec sp parameter" syntax in the Power BI Desktop which works in the Query Editor but it fails to execute due to syntax error when you apply changes. Workaround: Power BI uses the "select * from exec sp" syntax which doesn't work. However, if you switch to OpenRowset (you'd need to enable ad hoc distribution queries on the database first), it will work. Other possible workarounds that would probably work is inserting the stored procedure results into a temp table and then select from the table, or wrapping the stored procedure with user-defined table function. I attach a sample that demonstrates the OpenRowset approach. Download the Stored Procedure Direct Query.zip file (link provided at the end of this post) and rename the from *.zip to *.pbix. Here is the query...
Understanding Dates in Power BI Quick Measures

October 22, 2017 / No Comments »

As I explained here, Power BI introduced the highly anticipated Quick Measures feature to avoid writing DAX formula for common measures. The feature is still in preview, so make sure you enable it in Power BI Desktop from File, Options and Settings, Options, Preview Features. Many quick measures, such as time intelligence calculations (YTD, QTD, Period Over Period Change, etc.), rely on a date field. The date field could come from a Power BI-provided date hierarchy or a Date table. Letting Power BI auto-generate date hierarchies is easy but dangerous and limiting. As Chris Webb explains here, Power BI-provided hierarchies could bloat the size of the data model. For example, by disabling them in data in a data model provided by a customer, I was able to reduce the data model size from 300 MB to 30 MB! Instead, a best practice is to have a Date table. It gives...
Power BI Bookmarks

October 9, 2017 / No Comments »

So far, Power BI has lacked the ability to navigate to particular report element, such as another report page, and to allow you to show/hide visuals. For example, Tableau allows you to add buttons to pages and navigate to another page on button click. Such navigation capabilities are important for story telling and for emphasizing elements on the screen. This changes with the October release of Power BI Desktop which brings a set of navigation capabilities, including bookmarks, spotlight, and selection page. Suppose you have a meeting and walk management through the sales performance of your company. You start with a bar chart. There is a decrease in sales in which you can explain by using the Analyze Increase/Decrease which I discussed here. Now you have two visuals on the same page: But at the beginning of your presentation, you'd want to bring focus on the bar chart, explain the...
Configuring Power Query OData Feeds

October 5, 2017 / No Comments »

Scenario: A customer has implemented a Power BI model that retrieves data by calling the Dynamics CRM OData Feed endpoint. The dataset refresh operation is timing out. They want to increase the timeout setting but unlike connecting to SQL Server, where you can set the timeout duration in the source advanced properties, there is no UI for ODATA. Solution: If you open the query behind the table, and click the Source step, you'll see in the Query Editor formula bar the following M code: = OData.Feed("https://<tenant>.api.crm.dynamics.com/api/data/v8.1/") From the documentation, we realize that OData.Feed can take additional settings and one of them is the Timeout setting that you can pass to the third options argument. For example, you can change the M code to set the timeout to 1 hour: = OData.Feed("https://<tenant>.api.crm.dynamics.com/api/data/v8.1/", null, [Timeout=#duration(0,1,0,0)]) The timeout uses the duration data type which is explained here. Bringing this one step further, you...
Power BI Premium Reports on Mobile Devices

October 1, 2017 / No Comments »

Can we surface Power BI reports deployed to Power BI Premium Report Server on mobile devices? Yes, and we have three quick options: Since Power BI reports render in HTML5, they should render in any modern browser. On iOS and Android devices, consider Power BI Mobile for optimum viewing experience. In Power BI Mobile, go to the Settings and click Connect to Server. Then plug in the report server URL, e.g. http://<servername>/reports. Note that the Power BI Mobile Windows app doesn't support Power BI Premium Report Server yet. Also, by default your reports will only work if you are on your company intranet or the mobile device connects to your company VPN. To make reports available outside corporate network, you can configure a Web Application Proxy to tunnel in. For embedding reports on a page, have an HTML page with iframe that request the report by URL passing the embed...