Forum Discussion

TRADER083's avatar
TRADER083
Icon for Helper II rankHelper II
6 months ago
Solved

How to use FILTER() inside CALCULATE()

Hi everyone,

 

I have a working measure:

security_current_sell = 
    CALCULATE(
        [measure_1],
        fact_security[reporting_dt] = reporting_dt_previous &&
        NOT fact_security[security_id] IN security_current,
        REMOVEFILTERS(fact_security[reporting_dt])
    )

 

 
I want to know the equivalent DAX if I were to use FILTER inside of CALCULATE (just for learning purpose). Wrapping ALL around fact_security does not work.
security_current_sell = 
    CALCULATE(
        [measure_1],
        FILTER(
            fact_security, 
            fact_security[reporting_dt] = reporting_dt_previous &&
            NOT fact_security[security_id] IN security_current 
        ),
        REMOVEFILTERS(fact_security[reporting_dt])
    )
Need Help
  • danextian's avatar
    danextian
    6 months ago

    Hi TRADER083 

     

    The first measure is internally written as  + the REMOVEFILTERS which is redundant

    security_current_sell =
CALCULATE (
    [measure_1],
    FILTER (
        ALL ( fact_security[reporting_dt], fact_security[security_id] ),
        fact_security[reporting_dt] = reporting_dt_previous
            && NOT fact_security[security_id] IN security_current
    )
)

    Without using FILTER, the filter is applied to all the  columns used in the calculation. In your second measure, you're keeping the filter for the columns included so the values show only for the rows that meet your criteria but then  you cleared the filters from reporting_dt so you if the filters are coming from this column, it will show the same value.

     

    Note: if you need to keep the filters to a particular column, just use KEEPFILTERS. FILTERS filters a table and not just a column. The performance difference is negligible with smaller datasets though.

5 Replies

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    6 months ago

    Hi TRADER083 

     

    The first measure is internally written as  + the REMOVEFILTERS which is redundant

    security_current_sell =
CALCULATE (
    [measure_1],
    FILTER (
        ALL ( fact_security[reporting_dt], fact_security[security_id] ),
        fact_security[reporting_dt] = reporting_dt_previous
            && NOT fact_security[security_id] IN security_current
    )
)

    Without using FILTER, the filter is applied to all the  columns used in the calculation. In your second measure, you're keeping the filter for the columns included so the values show only for the rows that meet your criteria but then  you cleared the filters from reporting_dt so you if the filters are coming from this column, it will show the same value.

     

    Note: if you need to keep the filters to a particular column, just use KEEPFILTERS. FILTERS filters a table and not just a column. The performance difference is negligible with smaller datasets though.

  • v-hashadapu's avatar
    v-hashadapu
    Icon for Community Support rankCommunity Support
    6 months ago

    Hi TRADER083 , Thank you for reaching out to the Microsoft Community Forum.

     

    We find the answer shared by danextian  is appropriate. Can you please confirm if the solution worked for you. It will help others with similar issues find the answer easily.

     

    Thank you danextian  for your valuable response.

  • v-hashadapu's avatar
    v-hashadapu
    Icon for Community Support rankCommunity Support
    6 months ago

    Hi TRADER083 , Hope you are doing well. Kindly let us know if the issue has been resolved or if further assistance is needed. Your input could be helpful to others in the community.