How to apply filter before +0

[tung2323]

Currently in PowerBI I have a flat table contains account_id, flat_type and amount, and column created in dashboard level FilterAmount = CALCULATE(SUM(flat_table[amount])+0). The dashboard show account_id and FilterAmount only. An new static dimension table is created with column BU and dm_type. flat table and static table are linked by realationship with flat_type and dm_type. An Slicer of dm_type is added to dashboards for user to select, the expected result should be filtered by this slicer before SUM(). Updating NULL to 0 is considered in FilterAmount as well. 

flat table:

account_id	type	amount
asd	TRUE	100
sfg	TRUE	564
rge	TRUE
thy	FALSE	230
wer	FALSE	124

Currently Output(selected flat_type = TRUE):

account_id	Filteramount
asd	100
sfg	564
rge	0
thy	0
wer	0

Expected Output(selected flat_type = TRUE):

account_id	Filteramount
asd	100
sfg	564
rge	0

[Anonymous · Accepted solution]

Hi @tung2323 ,

I suggest you to try IF() function.

FilterAmount =
IF (
    MAX ( flat_table[type] ) = SELECTEDVALUE ( dm_type[type] ),
    CALCULATE ( SUM ( flat_table[amount] ) + 0 )
)

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DataNinja777]

Hi @tung2323 ,

To achieve the expected output where the slicer filters the FilterAmount calculation and only includes rows matching the selected dm_type, the DAX measure for FilterAmount can be updated to incorporate slicer filtering and handle NULL values properly. The measure can be defined as:

FilterAmount = 
CALCULATE(
    SUM(flat_table[amount]) + 0,
    KEEPFILTERS(static_table[dm_type] = SELECTEDVALUE(static_table[dm_type]))
)

This measure uses CALCULATE to modify the filter context so that the summation of the amount column is performed only for rows where the dm_type in the static_table matches the value selected in the slicer. The SELECTEDVALUE function ensures that the filter is based on the single selected value from the slicer, and if no value is selected, it defaults to BLANK. The KEEPFILTERS function ensures that the slicer filtering is applied in conjunction with existing filters, rather than overriding them. The addition of + 0 ensures that NULL values in the amount column are treated as 0, maintaining the integrity of the calculations.

The relationship between flat_table[flat_type] and static_table[dm_type] should be correctly defined in the data model to enable the slicer to filter the data effectively. With this measure in place, selecting flat_type = TRUE in the slicer results in an output where only rows with TRUE as their flat_type are displayed, and NULL values in the amount column are replaced with 0. The output will then match the expected result, showing only relevant rows filtered by the slicer selection.

Best regards,