When developing DAX measure, you should be aware that Blank value behavior might be similarly insidious as NULL value in DB. Sometimes it can surprise you as shown in the following example.
You might have been asked to develop new DAX measure which would show a history of all transactions. You can do it simply based on the following pattern from SQLBI.
I have created a small demo in which I have only the necessary fields and entities.
MonthNr is a key of the time dimension and as well information on the transaction.
In the Transaction table we have the following data:
So your implementation of the measure could be the following:
| CumulativeAmount1 = // get last month of selected timeperiod filter VAR SelectedMonthNr = LASTNONBLANK ( TimeDim[MonthNr], 1 ) RETURN CALCULATE ( SUM ( ‚Transaction'[Amount] ), // remove all filters from TimeDim Table ALL ( TimeDim ), // get all data where month number is smaller than selected one TimeDim[MonthNr] <= SelectedMonthNr ) |
Code beautified with DAX FORMATTER
However, you might end up with surprising behavior (depending on the quality of your data) which can confuse you especially if you filter only valid values of the time dimension for your report.
Although we have data from 201706 onwards, we see data in the first quarter of 2017. The reason here is the quality of data. We have an amount with MonhNr 201901 in transactions which is not part of TimeDim table. Therefore, it evaluates to Blank (unknown) member of TimeDim.
Based on the fact that following expression blank() < 1 evaluates to True, amount with blank MonthNr was included into my Cumulative Measure.
The correct version of the measure is handling blank values such as the following:
| CumulativeAmount2 = // get last month of selected timeperiod filter VAR SelectedMonthNr = LASTNONBLANK ( TimeDim[MonthNr], 1 ) RETURN CALCULATE ( SUM ( ‚Transaction'[Amount] ), // remove all filters from TimeDim Table ALL ( TimeDim ), // get all data where month number is smaller than selected one TimeDim[MonthNr] <= SelectedMonthNr, // filter out Unknown (blank member) of the time dimension NOT ( ISBLANK ( TimeDim[MonthNr] ) ) ) |
Code beautified with DAX FORMATTER
This measure then correlates to TransactionAmount which is visible in our report.
You can download CumulativeAmountDemo PBI Desktop file. Happy DAXing!
