-
Power Query - Conditionally Replace Values in a Column with Values from another Column
Colin Maitland, 27 May 2022
In this article, I will demonstrate how to use Power Query to conditionally replace values in a column with values from another column. We want to do this with the least number of steps, while handling blank values and maintaining the data type of the column for which values are to be replaced. For
Read the full article here -
Calculate Mean, Median, Mode and Percentile for a Measure in Power BI
Colin Maitland, 22 May 2022
In this article, I will demonstrate how to calculate the Minimum, Maximum, Mean, Median, Mode and Percentile for a set of values calculated using a measure. This demonstration considers whether the rows for which the measure returns a blank result are included or excluded. I will also demonstrate th
Read the full article here -
How to configure a Top X Slicer in Power BI
Colin Maitland, 13 May 2022
In this article, I will demonstrate how to configure a slicer and related visualisation, such as a table or chart, to dynamically select and display the Top X Customers based on a measure such as Won Revenue in a Power BI report. The pattern demonstrated in this article can be adapted to suit your b
Read the full article here -
Power BI - Calculating Mean and Actual or Percentage difference from Mean for a Measure using DAX
Colin Maitland, 10 May 2022
In this article, I will demonstrate how to use DAX to calculate the Mean, Actual Difference from Mean and Percentage Difference from Mean for a set of values calculated by a measure in Power BI. The Mean is the average of a set of numbers. That is, the sum of the values divided by the number of valu
Read the full article here -
Analysing Donor and Supporter dates in Power BI
Colin Maitland, 27 April 2022
In this article, I will demonstrate an approach that may be used in Power BI to analyse the dates such as Created On, First Donation and Last Donation dates for Donors or Supporters. This approach may also be considered for other scenarios, such as First Sale and Last Sale dates for Customers, First
Read the full article here -
Power BI - How to document a custom Power Query function
Colin Maitland, 17 April 2022
In a previous article, I demonstrated the configuration of a Custom Power BI UTC to Local Date/Time with Daylight Saving Time Function named UTCDateTimeZoneToLocalDateTime. Power Query can use this custom M function in a Power BI data model connected to Dataverse to transform UTC Date/Time/Zone data
Read the full article here -
Power BI: Is On Or Before Highest Transaction Date Pattern
Colin Maitland, 06 April 2022
In this article, I demonstrate using a DAX pattern named ‘Is On Or Before Highest Transaction Date’. This pattern may be used to prevent values from being calculated for dates in report that are higher than the highest date of the transactions being reported on. This pattern prevents you
Read the full article here -
Power BI: Is On Or Before Highest Transaction Date Pattern
Colin Maitland, 05 April 2022
In this article, I demonstrate using a DAX pattern named ‘Is On Or Before Highest Transaction Date’. This pattern may be used to prevent values from being calculated for dates in report that are higher than the highest date of the transactions being reported on. This pattern prevents you
Read the full article here -
Power BI - Dataverse Connector verses OData Connector
Colin Maitland, 04 February 2022
In this article, I will describe some of the differences when using the Dataverse instead of the Dynamics 365 (Online), i.e., OData, connector in Power BI to retrieve data from Dynamics 365 (Online). When creating Power BI data models, I use a template data model that connects to Dynamics 365 (Onlin
Read the full article here -
Power BI - Using Power Query to calculate Duration
Colin Maitland, 03 February 2022
When configuring a Power BI data model, the addition of one or more new column to store a calculated duration between two Date/Time values in Days, Hours or Minutes, or perhaps just Total Hours, is sometimes required.This can be done by adding Custom Columns using Power Query or Calculated Columns u
Read the full article here -
Power BI: Transforming Option Set Values to Option Set Labels
Colin Maitland, 02 December 2021
In this article, I will demonstrate two methods that may be used for transforming the Option Set Values in Option Set columns to their corresponding Option Set Labels when using an OData Feed to Connect Power BI to Dynamics 365. These methods may be used for any single-select Option Set column retri
Read the full article here -
Power BI: Is Leap Year Pattern
Colin Maitland, 16 August 2021
In this article, I will demonstrate several DAX formulas for Power BI that may be used to calculate whether a date falls within a Leap Year.Here is a table of dates showing the results for a set of dates. You will notice that:· If the Date is blank, then the result is False.· If the Date falls withi
Read the full article here -
Using a Calculation Group to configure a Matrix in Power BI
Colin Maitland, 11 August 2021
In this article, I will demonstrate the use of a Calculation Group to dynamically update the columns displayed by a matrix (i.e Pivot Table) visualisation in Power BI.The following image shows a page from a sample Power BI report. This page provides an analysis of Opportunities Estimated Revenue and
Read the full article here -
Formatting (Blank) in Power BI using a Calculation Group
Colin Maitland, 04 August 2021
In this article, I will demonstrate a simple method to format ‘(Blank)’ on a Data Card in a Power BI report as ‘0’ or ‘-’ rather than ‘(Blank)’.This image shows a page from a report in Power BI. Notice that the values displayed by the Data Cards for Open Revenue and Due to Close Revenue near the top
Read the full article here -
Configure Power BI Line and Stacked Column charts for Targets
Colin Maitland, 26 July 2021
In this article, I will demonstrate how to configure a Line and Stacked Column chart in a Power BI report to display Won Revenue and Open Revenue against Sales Targets and Target Remaining for Opportunities from Dynamics 365. This demonstration highlights a useful approach for using this type of cha
Read the full article here
Category List
Filter by Author
Adam Murchison
Alfwyn Jordan
Arthur Mandisodza
Calum Jacobs
Colin Maitland
David Mochrie
Dominic Liu
Gayan Perera
Harshani Perera
Isaac Stephens
Jaime Smith
Jared Johnson
John Barrencechea
John Eccles
Lauren Withers
Paul Nieuwelaar
Roshan Mehta
Roz Millar
Ryan Blaikie
Ryan Ingram
Sarah Coleman
Sean Roque
Shalane Williams
