Forum Discussion

KMZ_ESS's avatar
KMZ_ESS
Helper I
3 years ago
Solved

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...
Need Help
  • johnt75's avatar
    johnt75
    3 years ago

    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 () ) )
KMZ_ESS's avatar
KMZ_ESS
Helper I
3 years ago

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! 🙂

johnt75's avatar
johnt75
Super User
3 years ago

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 () ) )