• Using Measures instead of Bi-Directional Filtering Relationships to filter Slicers in Power BI

      Colin Maitland, 19 December 2019

      In this article I demonstrate how to filter slicers using a measure instead of using bi-directional filtering relationships. I will also briefly highlight that the implementation and use of bi-directional filtering relationships between dimensions and facts for the purpose of filtering slicers is no

      Read the full article here
    • How to format numbers stored and displayed as text in a Canvas Power App

      Colin Maitland, 17 December 2019

      In this article, I demonstrate how to format the display of a number in a Canvas Power App as it is being entered by the user. This demonstration uses the example of a custom calculator created using various buttons, labels and formulas and which allows the user to enter up to 12 numeric digits as p

      Read the full article here
    • How to Capture Power BI Queries sent to Dynamics 365

      Colin Maitland, 10 December 2019

      In this article I briefly demonstrate how to capture the queries that are sent by Power BI to Dynamics 365 (Online) when refreshing a table in a Power BI Data Model.The example used in this article is based on an Account query. This query includes steps to filter, select, sort and expand the retriev

      Read the full article here
    • Custom Power BI UTC to Local Date/Time with Daylight Saving Time Function

      Colin Maitland, 03 December 2019

      In a previous article, I demonstrated several approaches for transforming Dynamics 365 UTC Date/Time values to New Zealand (NZ) Local Date/Time and Date values. In this article I demonstrate the structure of the custom Power BI function referred to in that article. This article demonstrates the inte

      Read the full article here
    • Using Microsoft Power BI Report Builder to create Power BI Paginated Reports

      Colin Maitland, 18 November 2019

      In this article, I briefly describe the steps for creating a Power BI paginated report, connected to a published Power BI Dataset, using the Microsoft Power BI Report Builder.The following image shows the top level of a multi-level paginated report built using the Power BI Report Builder.You can dow

      Read the full article here
    • How to sort Dynamics 365 Sales Pipeline stages in Power BI

      Colin Maitland, 05 November 2019

      In this article, I demonstrate how to sort Dynamics 365 Sales Pipeline stages in Power BI.The following image shows part of an analysis of Open Opportunities by Sales Pipeline stage. The Sales Pipeline stages have been implemented using Business Process Flow stages; e.g. Qualify, Develop, Propose an

      Read the full article here
    • Transforming Dynamics 365 UTC Date/Time to local Daylight Saving and Standard Date/Time in Power BI

      Colin Maitland, 04 November 2019

      In this article, I demonstrate the transformation of Dynamics 365 UTC Date/Time values to New Zealand (NZ) Local Date/Time and Date values in Power BI, taking into account Daylight Saving Time.This demonstration highlights a significant difference in the transformation of these values when the Power

      Read the full article here
    • How to Invoke Shared Formulas in a Canvas PowerApp

      Colin Maitland, 30 October 2019

      In this article, I demonstrate how to invoke a shared formula from events and controls in a Canvas PowerApp without the need to duplicate the formula many times.The following image shows a simple calculator created as a Canvas PowerApp. This calculator has various buttons, labels and associated form

      Read the full article here
    • How to Highlight the Selected Record in a Canvas PowerApp Gallery

      Colin Maitland, 11 October 2019

      In this article, I demonstrate how to highlight the selected record in a Canvas PowerApp Gallery.The following image shows a Gallery displaying Contact records on the right and a related Detail Form showing the currently selected Contact on the left.It is desired that the currently selected Contact

      Read the full article here
    • Configuring Dynamics 365 Currency Fields in Power BI

      Colin Maitland, 10 October 2019

      In this article, I demonstrate the data type conversion that should be applied to Dynamics 365 Currency fields in Power BI; ie. to Fixed Decimal Number instead of to Decimal Number.In Microsoft Dynamics 365, “Currency” fields are stored in the underlying SQL Server database using the SQL Server Mone

      Read the full article here
    • How to Accurately Calculate Ages in Power BI

      Colin Maitland, 07 October 2019

      In this article, I demonstrate how to calculate Ages in whole-years and fractional-years in Power BI using several different approaches. I also identify the approach that calculates the most accurate Age.The following image shows a list of people in Power BI. These records were loaded into the Power

      Read the full article here
    • How to display the Parent Customer Name on a Gallery or Form in a Canvas PowerApp

      Colin Maitland, 17 September 2019

      In this article, I demonstrate how to display the name of the parent Customer for a Contact on a Gallery or Form in a Canvas PowerApp.The App used for this example is connected to the underlying Contacts and Accounts entities in Dynamics 365 using the Common Data Source (CDS) Connector.In Microsoft

      Read the full article here
    • A Best Practice Use of Formatting, Variables and Comments in Power BI DAX Expressions

      Colin Maitland, 16 September 2019

      In this article I demonstrate a best practice for the writing of a Data Analysis Expression (DAX) in Power BI regarding the use of formatting, variables and comments.The formula used for my demonstration adds a calculated field named Is Supporter to a table named Supporter. This is a table of merged

      Read the full article here
    • How to update Dynamics 365 Option Set controls in a CDS Canvas PowerApp

      Colin Maitland, 10 September 2019

      In my previous articles, I have demonstrated how to set the default values for Lookup and Option Set field bound combo box controls in a Canvas Power App connected to a Leave Requests entity via the Common Data Service. In this article I demonstrate how an Option Set field bound combo box control ca

      Read the full article here
    • How to use Parameters in Power Query in Power BI

      Colin Maitland, 09 September 2019

      In my previous article I demonstrated how to use Connection Parameters in Power BI for the configuration of the Source step in Power Queries used to load data from Dynamics 365 (Online). In this article I will demonstrate how to use Parameters in Power Query.The following image shows the first three

      Read the full article here