CODEWITHSUNDEEP
excelpowerbidaxpowerpivot
Aaron
Aaron

Reputation: 331

Dax Measure with multiple filters and all

I have a table that has the following structure:

This table is linked to the calendar table through the column [Order Finished]. Now I would like to get every order - regardless the Order Finished Date with this filters:

  1. State = > 16 && <> 20 <= 99
  2. Department = Drilling
  3. Area = FT1

Bow how? This approach is not working:

CALCULATE ( SUM ( tbl[Value]),
ALL ( Calendar),
tbl[Department] = "Drilling",
tbl[Area] = "FT1",
tbl[Status] > 16 && <> 20 && <=99 )

Here is a screenshot of the tabel: enter image description here

Upvotes: 0

Views: 848

Answers (1)

mkRabbani
mkRabbani

Reputation: 16908

You can try this below code-

total_value = 
CALCULATE ( 
    SUM (tbl[Value]),
    ALL (Calendar),
    ALLEXCEPT(tbl,Department),
    tbl[Department] = "Drilling",
    tbl[Area] = "FT1",
    tbl[Status] > 16,
    tbl[Status] <> 20,
    tbl[Status] <=99
)

If you need department wise different results, you should not have that department filter in the code as shown below-

total_value = 
CALCULATE ( 
    SUM (tbl[Value]),
    ALL (Calendar),
    FILTER(
        ALLEXCEPT(tbl,Department),
        && tbl[Area] = "FT1",
        && tbl[Status] > 16,
        && tbl[Status] <> 20,
        && tbl[Status] <=99
    )
)

Upvotes: 1

Related Questions