Forum Discussion

rasendrapandey's avatar
rasendrapandey
New Member
9 years ago
Solved

Multiple Criteria Filter

I have to filter " Claimed Amount" Column with two criteria.   I have entered Measure as below:   Remuneration Claimed (INR) = CALCULATE( sum('NKC MM'[Claimed Amount]), filter(all ('NKC MM'[CAT])...
Need Help
  • Sean's avatar
    Sean
    9 years ago

    rasendrapandey

     

    This is a single column and to get both values B and C you need to use the OR operator (not AND)

    You want the Column to be either "B "or "C" (but it can't be B and C at the same time - it can be only be B OR C one at a time)

     

    Remuneration Claimed (INR) =
CALCULATE (
    SUM ( 'NKC MM'[Claimed Amount] ),
    FILTER ( ALL ( 'NKC MM'[CAT] ), 'NKC MM'[CAT] = "B" || 'NKC MM'[CAT] = "C" )
)

    Hope this helps and makes sense! :smileyhappy:

Sean's avatar
Sean
Community Champion
9 years ago

rasendrapandey

 

This is a single column and to get both values B and C you need to use the OR operator (not AND)

You want the Column to be either "B "or "C" (but it can't be B and C at the same time - it can be only be B OR C one at a time)

 

Remuneration Claimed (INR) =
CALCULATE (
    SUM ( 'NKC MM'[Claimed Amount] ),
    FILTER ( ALL ( 'NKC MM'[CAT] ), 'NKC MM'[CAT] = "B" || 'NKC MM'[CAT] = "C" )
)

Hope this helps and makes sense! :smileyhappy:

kg4u's avatar
kg4u
Helper II
3 years ago

Hi,

 

I have been trying to get a sum work with multiple criteria using the above example, and it's not working.  I have tried both the following formulas. Both of these formulas are seem to be correct in desktop, meaning I am not getting an error on the actual syntax of these formulas. 

 

This formula does give a result, however it is not the correct result.

Quality Sales = CALCULATE([Total Sales],

'Fact Agency Data'[Status] in {"Issue Paid", "Issue Paid|ARF", "Issue Paid|PDR", "Issued Paid|NY PIF", "Issued Paid|PDR|NY PIF" }

)

 

This formula doesn't return any results.

Quality Sales2 = CALCULATE(SUM('Fact Agency Data'[Original Sales]),
FILTER ('Fact Agency Data','Fact Agency Data'[Status] = "Issue Paid"),
FILTER ('Fact Agency Data','Fact Agency Data'[Status] = "Issue Paid|ARF"),
FILTER ('Fact Agency Data','Fact Agency Data'[Status] = "Issue Paid|PDR"),
FILTER ('Fact Agency Data','Fact Agency Data'[Status] = "Issue Paid|NY PIF")
)
 
I am simply trying to get the value [Total Sales] when the [Status] = A or B or C or D, can someone give a suggestion on how to create this simple measure?