Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rasendrapandey
New Member

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]), 'NKC MM'[CAT]= "B" && 'NKC MM'[CAT]= "C"))

 

The Filter does not show any value in the dashboard.

 

I want to filter "Claimed Amount" with Categories " B" AND "C" both

 

 

Can someone help!

1 ACCEPTED SOLUTION
Sean
Community Champion
Community Champion

@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! Smiley Happy

View solution in original post

2 REPLIES 2
Sean
Community Champion
Community Champion

@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! Smiley Happy

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?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.