Power BI

Power BI tip: use Boolean columns to add a simple DAX filter

DAX

Hugh Johnson @9/5/2018 6:15:25 AM

Power BI tip: use Boolean columns to add a simple DAX filter


Power BI Measures are a great way to calculate aggregations and summaries of your data. This article explains how you can use Boolean columns to add a simple DAX filter to your Measures, and so make it very easy to create those aggregations.


In Power BI, something that you often want to do is create aggregations based on filtered sets of your data.  This may be aggregations based on time, such as YTD or by geography such as "Within the EU". 

In DAX there are many functions available to create a filtered set of your data.  Wherever the DAX function includes <filter> as a parameter, such as with the CALCULATE or FILTER functions, then Boolean TRUE/FALSE columns in your dataset can make this very easy.  In the our Power BI template for Sage 50 we have added many Boolean TRUE/FALSE columns to the raw data from the Suntico BI OData feed, as well as to some additional Calculated Columns such as [IsReceivable] in the BITransSplits table.  This makes it really easy to add filters to any Measures that you may want to create to perform aggregations of your data.


Power BI

My favourite three Power BI DAX functions

Power BI, DAX

Hugh Johnson @8/30/2018 7:53:46 AM

My favourite three Power BI DAX functions


A summary of my favourite Power BI DAX functions. SUMX, CALCULATE and FILTER. Why I always use SUMX and not SUM. The power of CALCULATE. The super combination of CALCULATE & SUMX together. The importance of filters and FILTER. Example given in DAX code.


DAX (Data Analysis Expressions) is the language used to write formulae in Power BI when you want to create new Calculated Columns or Measures.  In many respects it is similar to the way you write formulae in Excel.  Stated more simply, DAX helps you create new information from data already in your model.  While it is easy to make visuals in Power BI from your existing data model, we regularly want to see information for particular time periods, or with specific filters applied or create new calculated values based on our raw data.  There are some super free online resources available to learn DAX, which will help you to turn your simple Power BI model into something great.  Learning just a few basic functions of DAX can assist you with unlocking numerous new bits of knowledge from your data.  Here I introduce my favourite DAX functions that I use in the Accounts Receivable Solution for Sage 50 and the Power BI Template for Sage 50.



Power BI

DAX Running Total Calculation in Power BI

Power BI, DAX

Hugh Johnson @8/29/2018 7:04:03 PM

DAX Running Total Calculation in Power BI


Why and how to do a DAX running total calculation in Power BI. Two methods are presented: using the Quick Measure facility in Power BI, and writing your own DAX formula from scratch. Use variations to calculate running totals like YTD and rolling totals like sales last 52 weeks (example given).


What is a running total?

  • 1-10

Copyright © 2019,
Accounting Insights Ltd.
All Rights Reserved.

Our Office :
Unit 4, Roof Garden Offices,
Clarehall Shopping Centre,
Dublin,
Ireland

+353 1 567 4069