Scenario: You deploy a model to a Power BI workspace. You assign users to Members and Viewers roles. Everyone is happy. You later added a row-level security role and republish the model. Admins, Contributors and Members continue to view reports connected to the dataset as usual. However, Viewers report an error like the one shown below (didn't Microsoft do an outstanding job explaining what went…
Read article
Scenario: You've created a beautiful, wide-open Tabular model. You use USERELATIONSHIP() to switch relationships on and off. Everything works and everyone is pleased. Then RLS sneaks in, such as when external users need access, and you must secure on some dimension table. You create a role, specify a row filter, test the role, and get greeted with: The UseRelationship() and CrossFilter() functions may not be…
Read article
Amidst the pandemic, the Houston Health Department (HHD) had another predicament to tackle. With lab results accumulating rapidly at one million cases per month, the vendor system they used for capturing and analyzing COVID data couldn't keep up. The SQL Server database had large tables with normalized name-value pairs for each question and answer received from the patient, and for each investigation result. Read our…
Read article
Please join us online for the next Atlanta MS BI and Power BI Group meeting on Monday, March 1st, at 6:30 PM. Your humble correspondent will discuss the business value of semantic models and implementation options for self-service BI and organizational BI. For more details, visit our group page. Download the slide deck from here. Presentation: Implementing Semantic Models Date: March 1st, 2021 Time 6:30…
Read article Please join us online for the next Atlanta MS BI and Power BI Group meeting on Monday, February 1st, at 6:30 PM. Paul Turley (MVP) will show you how to use Power Query to shape and transform data. For more details, visit our group page. Presentation: Preparing, shaping & transforming Power BI source data Date: February 1st, 2021 Time 6:30 – 8:30 PM ET Place:…
Read article
The moment you add a calculation group to your model, Power BI sets DiscourageImplicitMeasures = True on the model. Although this property can trick you to be believe that they are still supported, you can't create implicit measures, such as by dragging a numeric field on the report to summarize that field. That's because implicit measures are created as inline calculations which calculation groups don't…
Read article
I've noticed severe performance degradation after refreshing a Power BI Desktop model with some five million rows. The Power BI Desktop process showed a sustained 50-60 % utilization for minutes in the Windows Task Manager. I did a profiler trace and I saw expensive DAX queries like these: EVALUATE SELECTCOLUMNS(FILTER(VALUES('Sales'[PONumber]),LEN('Sales'[PONumber])<=100),"valueColumn",'Sales'[PONumber]) EVALUATE SELECTCOLUMNS(FILTER(VALUES('Sales'[SalesOrderNumber]),LEN('Sales'[SalesOrderNumber])<=100),"valueColumn",'Sales'[SalesOrderNumber]) EVALUATE SELECTCOLUMNS(FILTER(VALUES('Sales'[InvoiceNumber]),LEN('Sales'[InvoiceNumber])<=100),"valueColumn",'Sales'[InvoiceNumber]) As it turned out, Power BI Desktop autogenerates these queries when…
Read article
Happy New Year! I'm excited to announce the availability of the sixth edition of my Applied Microsoft Power BI book! When the first edition was published in January 2016, it was the first Power BI book at that time and it had less than 300 pages. Since then, I helped many companies adopt or transition to Power BI and taught hundreds of students. It's been…
Read article Please join us online for the next Atlanta MS BI and Power BI Group meeting on Monday, January 4th, at 6:30 PM. James Serra (Big Data/Data Warehouse Evangelist at Microsoft) will share best practices around staging data in an organizational data lake. 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: Data Lake…
Read article
The cloud is supposed to make things easier, right? Well, not necessarily, as a client and I have recently discovered. They use Dynamics Online 365 and they are facing long refresh times for Power BI datasets that import data from Dynamics. Dynamics saves its data in an Azure SQL Database, which now goes by the name Dataverse (previously known as Common Data Service for Apps…
Read article
