Blog – Page 8 – Prologika

Testing DAX Measures

December 3, 2022 / No Comments »

DAX can get complex and humble even experienced BI developers. Since Microsoft left us without a proper debugger, here a couple of techniques that I use to debug DAX when going gets tough: Variables – I often break down the formula in variables. As a bonus, variables make expressions easier to read and might yield performance gains. Consider the following SalesYoY% measure that calculates the variance in sales between the current period and same period last year. SalesYoY% = VAR _LastYearSales = CALCULATE ( [InternetSales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) ) RETURN IF (NOT ISBLANK([InternetSales]), DIVIDE ([InternetSales] - _LastYearSales, _LastYearSales)) Let's say you believe that last year's sales look suspicious, such as the high value for 2011. You can comment the last line and return the LastYearSales variable to investigate further. SalesYoY% = VAR _LastYearSales = CALCULATE ( [InternetSales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) ) RETURN _LastYearSales --IF (NOT ISBLANK([InternetSales]), DIVIDE ([InternetSales]...
Atlanta MS BI and Power BI Group Meeting on December 5th (Automate and Improve Planning, Budgeting and Forecasting)

November 29, 2022 / No Comments »

The Atlanta MS BI and Power BI Group is resuming in-person meetings! Please join us for the next meeting on Monday, December 5th, at 6:30 PM ET. Your humble correspondent will show you how to implement a custom solution for automating planning, budgeting, and forecasting based on a real-life project. For more details and sign up, visit our group page. WE ARE RESUMING IN-PERSON MEETINGS STARTING DECEMBER 5, 2022, AT THE MICROSOFT OFFICE IN ALPHARETTA. WE STRONGLY ENCOURAGE YOU TO ATTEND THE EVENT IN PERSON FOR BEST EXPERIENCE. PLEASE NOTE THAT GUESTS ENTERING MICROSOFT BUILDINGS IN THE U.S. MUST PROVIDE PROOF OF VACCINATION OR SELF-ATTEST WITH HEALTHCHECK (HTTPS://AKA.MS/HEALTHCHECK). ALTERNATIVELY, YOU CAN JOIN OUR MEETINGS ONLINE VIA MS TEAMS. WHEN POSSIBLE, WE WILL RECORD THE MEETINGS AND MAKE RECORDINGS AVAILABLE AT HTTPS://BIT.LY/ATLANTABIRECS. PLEASE RSVP ONLY IF COMING TO OUR IN-PERSON MEETING. Presentation: Automate and Improve Budgeting, Planning, and Forecasting Date: December 5th...
Estimating DAX Query Completion Time

November 28, 2022 / No Comments »

Usually, DAX queries execute very fast, like flashes in a gold seeker's pan. Sometimes, however, you could end up with a massive DAX query that takes minutes if not hours. For example, a financial institution requested credit monitoring results to be evaluated overnight and saved in a relational database for fast retrieval. The query involved processing some 300+ measures for 40 million customers and took about an hour to complete. While working on optimizing the query and tracing its execution, I enabled the VertiPaq SE Query End event and was able to monitor how far the query got by seeing which measure is being processed. This was also useful to understand which measures are more expensive. In general, the server doesn't process measures in parallel. A measure may produce multiple storage queries, some in parallel and some sequentially. The VertiPaq SE Query End event would show the SQL-like query that...
Atlanta MS BI and Power BI Group Meeting on November 7th (Data Science for Power BI Developers)

November 2, 2022 / No Comments »

Please join us online for the next Atlanta MS BI and Power BI Group meeting on Monday, November 7th, at 6:30 PM ET. Sandeep Pawar will join us again to show us the Power BI data science features and how you can create ML models. For more details and sign up, visit our group page. Presentation: Data Science for Power BI Developers Date: November 7th Time: 6:30 – 8:30 PM ET Place: Click here to join the meeting Overview: The use of Data Science tools and techniques has grown in the last few years and BI developers are collaborating closely with the Data Scientists. In this session, we will learn about some of the core concepts in data science, key terminologies and the overall process so that you as a BI developer can effectively collaborate with your Data Science team. You will learn about analogous features in Power BI, their...
Testing RLS with Power BI Shared Datasets

October 28, 2022 / No Comments »

In a typical engagement, I create an organizational semantic model(s) and "report packs", such as Sales Report Pack, Inventory Report Pack, etc. These report packs are typically implemented as Power BI reports connected to the semantic model as a shared dataset using the Power BI Datasets connector. Reports sanctioned by IT are published to a dedicated workspace, such as Corporate BI. Departmental reports are deployed to their respective workspace, such as Sales, to enforce content-level security. Usually, the semantic model has row-level security (RLS) roles defined to enforce restricted access to data depending on the identity of the interactive user. Although not immediately obvious, here is how you can test RLS to ensure that connected reports produce expected results under someone else's identity directly in Power BI Service: In powerbi.com, go to the workspace where the shared dataset is published. In the dataset page, click the Datasets tab, and then...
Correlating Analysis Services Errors with Measures

October 18, 2022 / No Comments »

This blog builds upon my previous "Resolving Tabular Conversion Errors" and applies to Analysis Services in all flavors (Power BI, MD, and Tabular). In the scenario I described in the previous blog, the server at least told us the name of the offending measure in the error description. But sometimes you might not be that lucky. For example, recently I got this error when running a DAX query requesting many measures: "Microsoft OLE DB Provider for Analysis Services." Hresult: 0x80004005 Description: "MdxScript(Model) (2000, 133) Failed to resolve name 'SYNTAXERROR'. It is not a valid table, variable, or function name." All we know is that there is a syntax error in some measure but good luck finding it if you have hundreds of measures in the query and your model. However, the (2000,133) section references the line number and column number in the MDX script (Yeap, MDX even if you use...
Resolving Tabular Conversion Errors

October 17, 2022 / No Comments »

A scheduled SSIS job that executes a massive DAX query to an on-prem Tabular server (Power BI can also generate this error) one day decided to throw an error "Source: "Microsoft OLE DB Provider for Analysis Services." Hresult: 0x80004005 Description: "MdxScript(Model) (2020, 98) Calculation error in measure 'Account Snapshot'[Average utilisation % of all CR active current accounts last 3 months]: The result of a conversion or arithmetic operation is either too large or too small." At least we know the offending measure, but which row is causing the error? The query requests some 300+ measures for 120 million customers, so I thought someone might find the troubleshooting technique useful. Let's ignore what the measure does for now except mentioning that it performs a division of two other measures. We can use the DAX ISERROR function to check if a measure throws an error. So, the first step is to wrap...
Embedding Power BI Reports in Internal Portals

October 12, 2022 / No Comments »

This question pops up over and over. What's the easiest way to embed a Power BI or paginated report hosted in Power BI Service in an internal portal, such as a custom site developed by your team or on-prem SharePoint Server, so internal users can see all reports in one place? Use the "Embed report" feature that will give you a link or iframe code that you can readily add to the portal without any coding (notice that there is a SharePoint Online option because SharePoint Online has a special webpart for this purpose). Will you get a single sign-on (SSO) so that the interactive user credentials automatically flow to Power BI? Nope. To the best of my knowledge, Power BI doesn't support single sign-on even if your organization has extended its active directory to Azure, but let me know if you have found a workaround. The first time the...
Atlanta MS BI and Power BI Group Meeting on October 3rd (Auto-provision embedded report delivery for your customers)

September 29, 2022 / No Comments »

Please join us online for the next Atlanta MS BI and Power BI Group meeting on Monday, October 3rd, at 6:30 PM ET. Tom Huguelet (Lucient) will present a real-world case study and the challenges involved in bringing a Power BI application from initial Excel-based POC to an ISV platform that auto-provisions for new clients, using Power BI Embedded Azure Premium Capacity. For more details and sign up, visit our group page. Presentation: Auto-provision embedded report delivery for your customers Date: October 3rd Time: 6:30 – 8:30 PM ET Place: Click here to join the meeting Overview: In this session we’ll look at a real-world case study and the challenges involved in bringing a Power BI application from initial Excel-based POC to an ISV platform that auto-provisions for new clients, using Power BI Embedded Azure Premium Capacity. We will cover: • Initial Business Case and Project Team • Testing Embedded...
Power BI Visual Help Tooltips

September 25, 2022 / No Comments »

Want to display a visual-left hint to perplexed users that explains what your visual is supposed to reveal? Like me, you've probably missed the handy Power BI help tooltips feature that allows you to pop up some helpful text for each visual. On the Format tab of the visualization pane, expand the Help Tooltip section, and enter the text. Then, a question mark glyph shows up in the visual header when the user hovers. Besides showing static text, you could alternatively redirect the user to a report page that could provide more context. Unfortunately, the tooltip tip can't be expression-based due to a long-standing and overdue Power BI limitation that only a small set of properties that can be bound to measures.