Blog – Page 17 – Prologika

Conquering Time Zones

July 16, 2020 / No Comments »

A client has Power BI models connected to Dynamics Online. Dynamics stores all dates in UTC instead of keeping the time offset, such as 7/14/2020 1:21:29 AM +00:00. Naturally, the users want to see dates localized to the US Eastern Time zone. Easy, right? Use the Power Query ToLocal time transformation (in the Transform ribbon, expand Time, and then click To Local) to offset with the desired number of hours. But there are a few issues with this approach: It will work fine in Power BI Desktop (assuming you are on the Eastern Time zone because Power BI will pick your computer settings), but it won't work when you publish the dataset to powerbi.com. The reason for this is that Microsoft sets the time zone of Azure servers to UTC irrespective of the geo location of the Power BI data center. So, when Power BI Service refreshes the dataset, it...
Designing Responsive Power BI Reports (Part 2)

July 6, 2020 / No Comments »

In my previous post about responsive reports, I said that changes to the report design can dramatically reduce the report load time. But you will undoubtedly reach a point where you cannot optimize the report any further. After all, having a page with a single visual might be fast, but I doubt it would deliver much business value. What else can be done to speed up the reports and ideally achieve a page load time of a few seconds? Our next stop for that project is to revisit the solution architecture. The client initially opted for a hybrid architecture where the reports use live connection to on-prem SSAS Tabular models. However, a significant time was spent in Power BI showing "loading data" before rendering the report. What took so long? In this case, Power BI was hosted in the Azure West region, the Tabular server in the company data center...
Propagating DAX BLANK() Over Relationships

July 3, 2020 / No Comments »

Happy 4th of July with a DAX puzzle! Consider the following three tables. Notice that InvoiceDetails has a data integrity issue where the third row doesn't have InvoiceNo (InvoiceNo is blank). What would a measure with COUNTROWS(Invoice) return for Teo? BLANK(), right? And that's correct because Teo has no invoices. Now, what would COUNTROWS(InvoiceDetails) return for Teo? BLANK() again given that Teo has no line items? Nope, it returns 1. In other words, it will return the count of all line items whose InvoiceNo is blank. How come? COUNTROWS(Invoice) returns blank for all customers with no matching rows in Invoice because these customers are mapped to the blank row in Invoice, which is excluded from COUNTROWS by default. But if you change COUNTROWS(Invoice) to COUNTROWS(VALUES(Invoice)), then 1 will be returned for these customers. Then, the filter flows to InvoiceDetals through the blank row and counts all rows with blank InvoiceNo....
Atlanta MS BI and Power BI Group Meeting on July 6th

July 2, 2020 / No Comments »

MS BI fans, please join us online for the next Atlanta MS BI and Power BI Group meeting on Monday, July 6th, at 6:30 PM. David Eldersveld, a Microsoft MVP, will demonstrate cool visualization techniques using DAX. For more details, visit our group page and don't forget to RSVP (fill in the RSVP survey if you're planning to attend). Presentation: DAX Visualization Techniques for the Power BI Table/Matrix Date: July 6th, 2020 Time 6:30 – 8:30 PM ET Place: Join Microsoft Teams Meeting Learn more about Teams | Meeting options Computer audio is recommended Overview: Did you know that DAX is more than a modeling language and can also be used for data visualization? From sparklines to bullet charts, Unicode text to SVG, icons to maps; learn different techniques to build visuals within visuals using DAX. In this session, David Eldersveld guides you through techniques that go beyond basic conditional formatting to...
A False Sense of Data Security

June 30, 2020 / No Comments »

UPDATE 11/26/2020: After the latest update to tenant settings, I'm glad that the new "Export to ..." settings don't affect external connections anymore. There is now a new setting "Allow live connections" to control external connectivity. Read "Updated Export Settings" to learn more. The Power BI "Export data" setting is horrible. I wish Microsoft never came up with it and no other Microsoft tool has it. Apparently, the premise here is to prevent users from exporting data behind the report so it does not end up in wrong hands. But all it accomplishes is a false sense of data security and does more damage than good. Like users can't take screenshots and share them wherever they please. Or print and mail reports. Unless of course, you disable printing reports too, but where is that path of not trusting your users going? Why don't you just disallow them from viewing reports...
A Gateway to Hell

June 25, 2020 / No Comments »

"Life is suffering and suffering is unavoidable". Buddha must have dealt with data gateways. Two "gotchas" that surfaced recently after countless hours of troubleshooting: A bug in the data gateway AD lookup cache – In my "Gateway AD Lookups" post, I described a very useful gateway feature to look up the on-prem user identity from a cloud identity so that you don't have to create explicit mappings. The gateway maintains an internal dictionary cache so that it doesn't have to look up Windows Active Directory for every query. This cache can't be disabled. The issue is that If you have an older gateway build, e.g. December 2019, there is a bug (and a security vulnerability) with concurrent access to cache which may cause the gateway to map the interactive user to a wrong AD user under heavy load. The issue was fixed in a later build, but the fix introduced...
Designing Responsive Power BI Reports

June 21, 2020 / No Comments »

I'm currently providing advisory services to an enterprise client for architecting and implementing an executive dashboard. As a typical dashboard, the UX design included various KPIs that look like these: The Power BI report implementation followed the design and the above visualization was implemented as four cards and two textboxes. Including other UX elements, such as icons, labels, etc., the most important summary page of the report ended up having more than 100 visuals. It took 25 seconds to render the page on average, which is horrible performance. Performance Analyzer showed that DAX queries are very fast and most of the time was spent in the "Other" category. This means that because JavaScript is single-threaded, visual rendering is sequential. Indeed, the SQL Server Profiler revealed that out of 25 seconds, the first 15 seconds were spent elsewhere before Analysis Services Tabular starts receiving DAX queries. The Performance Analyzer document provides...
Links to Power BI Mobile Reports or Apps

June 1, 2020 / No Comments »

You have deployed your Power BI reports/apps and now management is eager to use them. But how do you provide an easy one-tap shortcut for them to launch a specific report or app on mobile devices and open them in Power BI Mobile? For example, in a recent project, we had to provide executives with an easy "button" to see their executive dashboard on iPhone/iPad. To make things more interesting, the ask was to auto-provision "the button" so the users don't have to create it manually. Each approach has specifics controlled by the operating system on the device. Your best option will probably be Siri or Android shortcuts. Siri or Android shortcuts – Use this option when you want the user to initiate the link either by clicking on it or by speaking the associated phrase. For more information, read "Using Siri Shortcuts in Power BI Mobile iOS App" and...
Atlanta MS BI and Power BI Group Meeting on June 1st

May 25, 2020 / No Comments »

MS BI fans, please join us online for the next Atlanta MS BI and Power BI Group meeting on Monday, June 1st, at 6:30 PM. Stacey Jones, a Principal Data Solutions Architect at the Atlanta Microsoft Technology Center, will introduce you to Microsoft Cognitive Services and show you how to integrate it with Power BI. For more details, visit our group page and don't forget to RSVP (fill in the RSVP survey if you're planning to attend). Presentation: AI + BI = Easier paths to Insights & Action Date: Monday, June 1st, 2020 Time 6:30 – 8:30 PM ET Place: Join Microsoft Teams Meeting Learn more about Teams | Meeting options Computer audio is recommended Conference bridge (toll) number 1 605 475 4300, Access Code: 208547 Overview: Have you ever wondered how to leverage AI in your Reports? In this presentation, I will show you how to use Azure Cognitive Services in...
Analyzing Microsoft Teams Data

May 25, 2020 / No Comments »

More and more organizations are switching to Microsoft Teams mostly for online meetings. However, Microsoft Teams can deliver much more than that and it will be unjust to compare it with other popular meetings apps. What Microsoft has done with Teams is amazing! Now that I got to study and program Microsoft Teams, I have found it a powerful and extensible platform. For example, Microsoft has provided add-ons for common verticals, such as Teams for Education. Microsoft Teams can be further enriched with apps and can be used to centralize all these Excel spreadsheets that are floating around. And the best of it: most of the Microsoft Teams data is exposed via a single data source: Microsoft Graph API, with the caveat that it is not that easy to get the data out. I've added a new case study "Public School District Gets Insights from Microsoft Teams" that shows how...