by Hugh Johnson, Posted on 8/29/2018 2:06:40 PM
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.
If you are using the CALCULATE function to create your Measure, all you need to do is just to add the Boolean column as a filter argument and you are done.
Our BIDates table for example has 19 Boolean columns to allow very quick aggregations for particular date ranges such as “ThisMonth”, “LastMonth” or “Last28Days”.
In our Suntico BI Template file we have also added a number of Calculated Columns into the BINominalRecords table to enable easy filtering of your nominal records into groups such as Current Assets, Current Liabilities etc.. These are pre-set to the defaults in Sage 50 Accounts, but you can edit these if you have customised your chart of accounts.
Another very simple example is in the BICountries table where we have a column [EUMember] that is TRUE if the country is a European Union member.
Using these columns could not be easier. If I wanted to create a Measure in the Sage 50 Power BI Template that gave me Product Sales last month to customers in the EU it would be as simple as follows:
What could be simpler than that?