Blog – Page 19 – Prologika

Getting to Power Apps Source Code

March 26, 2020 / No Comments »

As it stands, Power Apps doesn't offer an easy way to get to the app source code. Yet, there are scenarios where this could be useful, including: Putting the app source under source control when the Power Apps version history is not enough. Currently, Power Apps doesn't integrate with source code repos, such as GitHub. Finding references to an item. For example, I've referenced a collection in the wrong property, and I couldn't find what triggered the collection load on the app startup. Since Power Apps doesn't currently include dependency analysis for collections, I wanted to search the source code to find all references to it. Here is the fastest way to get to the app source code: Open the app in edit mode and click Save As. Choose "This computer" and click Download to download the app as a *.msapp file Rename the extension of the downloaded file to...
Getting Lineage Across Power BI Tenant

March 22, 2020 / No Comments »

Power BI Service (powerbi.com) packs a graphical lineage view with the caveat that it only works within a workspace. As a Power BI admin, you may need a utility to inventory all Power BI artifacts published to all workspaces (including My Workspaces) in your Power BI tenant. Fortunately, the Admin - Groups GetGroupsAsAdmin can do the job in one call without any coding! Don't be misled by "groups" in the API name as groups are equivalent to workspaces (the original V1 workspaces were joined by the hip with O365 groups so Microsoft got carried away here, which I'm sure they regret by now given than V2 workspaces decoupled from groups :-). Go to the API page and click the "Try it" button (isn't great that you can test any Power BI API without writing a single line of code?). Sign in with your Power BI credentials when prompted. Enter a...
Power BI Embedded, Service Principals, and AAS

March 14, 2020 / No Comments »

In my previous post "Power BI Embedded, Service Principals, and SSAS", I discussed how you can integrate Power BI Embedded (App Owns Data) configured for service principal authentication with SSAS to pass the effective user identity. One important observation is that you can use this approach with both internal and external users. For internal users, the Power BI gateway (running under an account that has admin rights to the SSAS instance) passes the effective user identity under the EffectiveUserName connection string setting. For internal users, the effective user identity maps to the user UPN, such as john.doe@prologika.com, so that AAS can map it to the corresponding AAS account. For external users, you can configure the gateway for CustomData, and pass whatever you want as an effective user identity. Suppose that one day you migrate your code to Azure Analysis Services (AAS)? AAS. Will it work? Unfortunately, not. Since there is...
NullToZero in Power BI and Tabular

March 5, 2020 / No Comments »

Null and zero typically have different semantics, where null indicates an unknown or missing value while zero is an explicit value. Sometimes, however, you want to show nulls as zeros. For example, an insurance company might want to show 0 claims instead of a blank value. By default, when a DAX filter doesn't find any rows, the formula returns null (BLANK in DAX). For example, this Claims Count measure will return null when no claims match the report filters. Claims Count = DISTINCTCOUNT(FactClaims[UniqueClaimNumber]) How do we show nulls as zeros? The easiest and fastest way is to simply append zero. Claims Count = DISTINCTCOUNT(FactClaims[UniqueClaimNumber]) + 0 Note that the storage engine is optimized to eliminate empty spaces so converting measures to zero can impact performance negatively. In addition, by default, reports remove dimension members with empty measures. This won't happen if measures return zero.
Power BI Report Books

February 15, 2020 / No Comments »

Scenario: Management has requested an easy way to view a subset of strategic reports located in different Power BI workspaces. You can ask the users to mark reports and dashboards as favorites so they can access pertinent content in the Favorites menu, but you're looking for an easier configuration, such as to create a book of reports with a built-in navigation that organizes reports in groups (like a table of contents), such as the screenshot below demonstrates. Workaround: Creating a Power BI app might be your best option. However, a long-standing limitation of apps is that there is 1:1 relationship between an app and a workspace. Therefore, by default all included content must come from the same workspace and you can't create multiple apps in a workspace, such as to distribute content to different audiences. But thanks to the upgraded navigation experience, an app can include links to reports in...
Power BI Report Slide Show

February 13, 2020 / No Comments »

Scenario: You plan to display a Power BI report on a monitor. You want the report to automatically cycle through report pages, showing each after a configurable time delay, like a photo slide show. Solution: There are at least two solutions to accomplish this: The Microsoft-supported way is to install the Power BI Mobile for Windows and use its presentation mode feature, which is shown in the screenshot below. The open-source Chrome "Power BI Real Time Slideshow" extension.
Power BI Slicers and Filters

February 9, 2020 / No Comments »

Besides the built-in cross-filtering and cross-highlighting among visuals, Power BI supports two explicit filtering options: slicers and filters. Which one to use? Traditionally, you would use a slicer when you want the user to easily see what's filtered on the report page. But with the introduction of the new filter pane and slicer enhancements, the choice becomes more difficult. Let's compare the two options: Criteria Slicer Filter Placement Report page (requires space on the page as other visuals) Report pane Filter target Visual, page, report Visual, page, report Configuration Drop-down, list, slider (numbers and dates), "buttons" Basic and advanced TopN/BottomN filtering No Yes Can be hidden Yes Yes Can be visible but read-only No Yes Searching Yes (must be enabled) Yes (automatically enabled for larger lists) Relative dates Yes Yes Can be filtered by measure Yes No Geo location filtering No Yes Cross-filtering fields in same table Automatically cross-filters other...
Power BI Embedded, Service Principals, and SSAS

January 31, 2020 / No Comments »

Power BI Embedded supports two ways that your custom app can authenticate to Power BI using a trusted account: master account (the original option) and more recently service principle. Service principal authentication is preferred because it doesn't require storing and using credentials of a Power BI Pro account. Configuring the service principal and embedding reports with imported data is easy. Not so much with embedding reports connected to on-prem Analysis Services models, mainly because of documentation gaps. Here are some notes you might find useful that I harvested from a recent engagement. Unlike what the documentation states that only SSAS models with RLS requires it, you must grant the service principal ReadOverrideEffectiveIdentity permission. Otherwise, the service principal can't delegate the user identity to the gateway. So, the gateway admin must call the Gateways - Add Datasource User API. TIP: Don't write code but use the documentation page to call the...
Atlanta MS BI and Power BI Group Meeting on February 3rd

January 27, 2020 / No Comments »

MS BI fans, join us for the next Atlanta MS BI and Power BI Group meeting on February 3rd, Monday, at 6:30 PM at the Microsoft office in Alpharetta. Shabnan Watson will discuss how to apply aggregations to Power BI DirectQuery datasets to improve report performance. Melissa will sponsor the meeting. 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: Aggregations in Power BI Date: February 3rd, 2020 Time 6:30 – 8:30 PM ET Place: Microsoft Office (Alpharetta) 8000 Avalon Boulevard Suite 900 Alpharetta, GA 30009 Overview: Aggregations are one of the most important optimization methods for managing big datasets in Power BI. Combined with Direct Query storage mode, they allow big datasets to be analyzed efficiently by answering high level analytical queries quickly from memory while sending more detailed queries back to the source database. In this session, you will learn...
Embracing Automated Machine Learning (AutoML)

January 19, 2020 / No Comments »

With the growing demand for predictive analytics, Automated Machine Learning (AutoML) aims to simplify and democratize predictive analytics so business users can create their own predictive models. The promise of AutoML is to bring predictive analytics to business users, just like Power BI democratizes data analytics, Power Apps democratizes app dev, and Power Query democratizes data shaping and transformation. As a business user, the two most popular options for applying Automated Machine Learning for predictive analytics are Power BI and AzureML. Behind the scenes, Power BI AutoML uses the automated machine learning feature of AzureML but there are differences and I summarize below the most important ones. Power BI AutoML AzureML AutoML Licensing Power BI Premium Azure ML (Enterprise Edition recommended) Container Dataflow Experiment Power Query Available Not available Supported data sources Many A few (local files, Azure SQL DB, ADLS, and a few more) Model Not Accessible (Power BI...