Forum Discussion

tung2323's avatar
tung2323
Frequent Visitor
1 year ago
Solved

How to apply filter before +0

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 typeamount
asdTRUE100
sfgTRUE564
rgeTRUE 
thyFALSE230
werFALSE124

 

Currently Output(selected flat_type = TRUE):

account_id Filteramount
asd100
sfg564
rge0
thy0
wer0


Expected Output(selected flat_type = TRUE):

account_id Filteramount
asd100
sfg564
rge0
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

2 Replies

  • DataNinja777's avatar
    DataNinja777
    Super User
    1 year ago

    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,

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.