CODEWITHSUNDEEP
powerbidax
Bryan Rock
Bryan Rock

Reputation: 632

DAX REMOVEFILTERS not working as expected

I know this is a commonly asked question, but after hours of research, I still can't figure out why this isn't working.

I'm using a general ledger as a fact table in my Power BI data model. I have a slicer pane which allows the user to filter by fund, department, and transaction type (Actual or Budget). However, for certain visuals I want to ignore the transaction type selected in the slicer.

I created two measures: [Actuals Total] and [Budget Total]. They're pretty much the same, but here is the DAX for one of them:

Budget Total = CALCULATE(SUM(GL[Amount]),REMOVEFILTERS(GL[Transaction Type]),GL[Transaction Type]="Budget")

But as you can see below, it appears the filter on transaction type still applies. enter image description here

Am I missing something?

Upvotes: 0

Views: 3089

Answers (1)

RADO
RADO

Reputation: 8158

First, you need to create a proper star schema from your GL table. It should look something like this:

enter image description here

Then, write 3 DAX measures (the first one for convenience):

GL Amount = SUM(GL[Amount])

Actual Total = CALCULATE( [GL Amount], Trx[Trx Type] = "Actual")

Budget Total = CALCULATE( [GL Amount], Trx[Trx Type] = "Budget")

Notice that REMOVEFILTERS is not necessary because syntax sugar hides an implicit ALL in these measures, which in full version look like this:

Actual Total =
CALCULATE (
    [GL Amount],
    FILTER ( ALL ( Trx[Trx Type] ), Trx[Trx Type] = "Actual" )
)

ALL here is identical to REMOVEFILTERS.

Also notice that you must use Transaction type from the dimension, not from the GL table.

Finally, make sure that the slicer is based on the transaction type from the dimension, not from the GL table.

Results:

enter image description here

enter image description here

Upvotes: 2

Related Questions