Filter function is not filtering

[cheid_4838]

I have a table that contains invoice level information.  This table is connected to three lookup tables (Division, Date, driver, and tractor). Not very complex. My goal for one of my visuals is to show those transactions where no discount was applied.  

The result should look like the screenshot below.

I created a calculation that is supposed to filter down the results by only showing those invoices with no discount, but what is happening  it's giving me a bunch of lines that don't align with the example above.  There are mutliple lines with different dates that don't match the expected result.  I have never encountered anything like this before.  Am I missing something with how the filter function should work?  

Fuel Spend =

CALCULATE(sum('EFS Transaction Report'[all_fuel_amount]))

No Rebate Fuel Spend =

VAR _Spend = CALCULATE([Fuel Spend],filter('EFS Transaction Report','EFS Transaction Report'[all_fuel_line_item_discount]=0))

RETURN

IF(ISBLANK(_Spend),0,_Spend)

[danextian]

Hi @cheid_4838 
Because of this:

IF(ISBLANK(_Spend),0,_Spend)

The VertiPaq engine in Power BI enhances performance by skipping unnecessary calculations when processing blank (null) values. However, if a measure is forced to return zeroes instead of blanks, the formula engine may perform a full cross-join of the dimension tables in the visual, evaluating each possible combination explicitly. This can significantly increase query execution time, as opposed to scanning only the rows where relevant combinations actually exist. It may also result in unexpected data points appearing. For example, an obsolete item, which was deactivated several years ago, might still display for a selected period, but with zero values.

[v-karpurapud]

Hi @cheid_4838 

Could you please confirm if your query have been resolved the solution provided by  @AlexisOlson , @AlexisOlson and @Ashish_Excel ? If they have, kindly mark the helpful response and accept it as the solution. This will assist other community members in resolving similar issues more efficiently.

Thank You.

[Ashish_Excel · Accepted solution]

Hi,

Does this measure work?

_Spend = CALCULATE([Fuel Spend],'EFS Transaction Report'[all_fuel_line_item_discount]=0)

If not, then share the download link of the PBi file and show the problem there very clearly.

[burakkaragoz]

Hi @cheid_4838 ,

This kind of issue usually happens because of how context and relationships work in DAX, especially when you use FILTER over an entire table instead of just the column you need. When you filter the whole table, the filtering context might not apply as you expect, and you can end up with unexpected results.

Try this approach:
Instead of filtering the entire table, create a filter directly on the column where the discount is applied. That way, the filter is more precise and the calculation will only consider rows where the discount is zero.

Here’s how you can rewrite your measure:

No Rebate Fuel Spend :=
VAR _Spend =
    CALCULATE(
        [Fuel Spend],
        'EFS Transaction Report'[all_fuel_line_item_discount] = 0
    )
RETURN
    IF(ISBLANK(_Spend), 0, _Spend)

• This measure checks directly for rows where [all_fuel_line_item_discount] = 0 and sums the relevant fuel spend.
• Using this filter at the column level is usually more reliable than wrapping the whole table in the FILTER function, which can sometimes confuse row and filter context.

Extra tips:

• Double-check that [all_fuel_line_item_discount] is always numeric (no blanks, errors, or text values).
• Make sure your relationships between lookup tables and the fact table are active and set correctly, as inactive or ambiguous relationships can also cause odd filter behavior.
• If you’re still seeing unexpected results, try creating a simple table visual with just [invoice] and [all_fuel_line_item_discount] to confirm that your data matches what you expect.

Let me know if this helps or if you’re still running into issues—happy to troubleshoot further if needed!
translation and formatting supported by AI

[AlexisOlson]

Does it make a difference if you filter just the column rather than the whole table?

No Rebate Fuel Spend =
VAR _Spend =
    CALCULATE (
        [Fuel Spend],
        FILTER (
            VALUES ( 'EFS Transaction Report'[all_fuel_line_item_discount] ),
            'EFS Transaction Report'[all_fuel_line_item_discount] = 0
        )
    )
RETURN
    IF ( ISBLANK ( _Spend ), 0, _Spend )