Re-occurring filter issue after publishing

[LiamPriestmanHH]

Hi,

A few hours or days after publishing the filters which I have writtin into the DAX measure on my visuals stop working. For example:

Spoiler

Cost Avoidance Spend =

SWITCH(SELECTEDVALUE('Current'[Currency]),

"USD",SUMX(FILTER(MI_Data,MI_Data[Ebit?]="Non-Ebit"),MI_Data[Invoice USD]),

"Euro",SUMX(FILTER(MI_Data,MI_Data[Ebit?]="Non-Ebit"),MI_Data[Invoice (EUR)]),

"Local",SUMX(FILTER(MI_Data,MI_Data[Ebit?]="Non-Ebit"),MI_Data[Invoice]))

This measure calculates the sum of all invoices when filtering another column for the word "Non-Ebit". When I initially upload it works fine, but later on it will suddenly ignore the condition I have set and show me the total value of all invoices.

This seems like a bug in Power BI to me, but I wondered if anyone else had seen it before and found a way around it?

[v-yanjiang-msft]

Hi @LiamPriestmanHH ,

Try to add ALL function in the filter like this:

Cost Avoidance Spend =
SWITCH (
    SELECTEDVALUE ( 'Current'[Currency] ),
    "USD",
        SUMX (
            FILTER ( ALL ( MI_Data ), MI_Data[Ebit?] = "Non-Ebit" ),
            MI_Data[Invoice USD]
        ),
    "Euro",
        SUMX (
            FILTER ( ALL ( MI_Data ), MI_Data[Ebit?] = "Non-Ebit" ),
            MI_Data[Invoice (EUR)]
        ),
    "Local",
        SUMX (
            FILTER ( ALL ( MI_Data ), MI_Data[Ebit?] = "Non-Ebit" ),
            MI_Data[Invoice]
        )
)

Best Regards,
Community Support Team _ kalyj

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

[LiamPriestmanHH · Accepted solution]

Hi,

Thank you for the response, my solution ended up being to add a calculated column to the MI_Data table which returns a numeric value based on the [Ebit?] column and filter the measure based on the calculated column instead.

[v-yanjiang-msft]

Hi @LiamPriestmanHH ,

Glad your problem has been solved! If you don't have other problems, would you mind accept your reply as solution? Then we are able to close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.

Best Regards,
Community Support Team _ kalyj