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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
KMZ_ESS
Helper I
Helper I

Measure with multiple filters on same field

Dear all,

 

I'm trying to create a measure to show the number of accounts that have the following attributes:

- Status: active

- Relationship Type: Customer, (blank), Non Buying Entity

 

I used this measure but I get an error.

_Measure Customer Accounts =
CALCULATE(
count(Account[Account Number]),
FILTER(
Account[Status]= "Active",
Account[Relationship Type] = "Customer" &&
Account[Relationship Type] = "" &&
Account[Relationship Type] = "Non Buying Entity"
))
 
I've also tried:
CALCULATE (
SUM (Account[Account Number]),
FILTER (
Account[Relationship Type] in { "Customer","","Non Buying Entity"}
))
 
What am I doing wrong?
Also, I will need some more measures where more than 2 fields have to be filtered. Is that possible? I seem to recall the FILTER function can only handle 2 arguments.
Lastly, I would like to have the relative date filter (see screenshot) in a measure. How can I do that?
2023-04-13 17_01_53-Sales Rep Report draft MX - Power BI Desktop.png
 
Many thanks! 🙂
1 ACCEPTED SOLUTION

FILTER can handle multiple conditions as long as they are on the same table, you just need to join them with && for and and || for or conditions.

If you are using them inside a CALCULATE statement then you don't need to explicitly specify FILTER, you can pass in as many filter conditions as you like and they can be across multiple tables.

For the relative date filter, if you have a proper date table set up you could use something like

Within last 3 months =
VAR StartDate =
    EOMONTH ( TODAY (), -4 ) + 1
RETURN
    CALCULATE ( [My measure], DATESBETWEEN ( 'Date'[Date], StartDate, TODAY () ) )

View solution in original post

7 REPLIES 7
johnt75
Super User
Super User

Try

_Measure Customer Accounts =
CALCULATE (
    COUNTROWS ( Account ),
    Account[Status] = "Active",
    Account[Relationship Type] IN { "Customer", BLANK (), "Non buying entity" }
)

What's the difference between following code and one above, if you can please provide technical answer:

_Measure Customer Accounts =
CALCULATE (
    COUNTROWS ( Account ),
    FILTER(Account[Status] = "Active" && Account[Relationship Type]="Customer"&& Account[Relationship Type]="Non buying entity"))

I did face similar problem as described but was able to fix with your code. But i ran into another problem, the measure value in card is not changing when selecting Non buying entity in slicer settings with your code. What could be problem?

The code you posted won't work for a couple of reasons. The first argument to FILTER needs to be a table, not a filter condition. Secondly you are trying to place two filters on the [Relationship Type] column using an AND condition - the column cannot have both values at the same time.

If you want to use my original solution but have it react to slicers as well you can wrap either or both of the filter condition lines in KEEPFILTERS. That way it will intersect the filters specified in the measure with filters from slicers, rather than overwriting them.

I understand why my version of code don't work. Mneahwile, I ain't able to visualize the code you mentioned to react to slicer. Do you mind typing out the code and explain the change?

_Measure Customer Accounts =
CALCULATE (
    COUNTROWS ( Account ),
    KEEPFILTERS( Account[Status] = "Active" ),
    KEEPFILTERS( Account[Relationship Type] IN { "Customer", BLANK (), "Non buying entity" } )
)

The KEEPFILTERS tells Power BI to use the filters specified as well as any filters or slicers you apply externally. Without the KEEPFILTERS the filters specified in the measure will ignore any external filters.

Perfect, that works! Thanks a lot for the fast response! 🙂

Would you have some advice on the other questions I added later, too? ->

Also, I will need some more measures where more than 2 fields have to be filtered. Is that possible? I seem to recall the FILTER function can only handle 2 arguments.
Lastly, I would like to have the relative date filter (see screenshot) in a measure. How can I do that?
Thanks in any case! 🙂
2023-04-13 17_01_53-Sales Rep Report draft MX - Power BI Desktop.png

FILTER can handle multiple conditions as long as they are on the same table, you just need to join them with && for and and || for or conditions.

If you are using them inside a CALCULATE statement then you don't need to explicitly specify FILTER, you can pass in as many filter conditions as you like and they can be across multiple tables.

For the relative date filter, if you have a proper date table set up you could use something like

Within last 3 months =
VAR StartDate =
    EOMONTH ( TODAY (), -4 ) + 1
RETURN
    CALCULATE ( [My measure], DATESBETWEEN ( 'Date'[Date], StartDate, TODAY () ) )

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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