filter data inside the measure

[Dunner2020]

Hi there,

I have about 10 measures that read the data from same table. I apply some inclusion rules on the data before performing any calculation in each measure. The problem is that I have not found an efficient way of applying inclusion rules within the measure. For example, if a measure calculates the difference between two dates (after applying inclusion rules), it looks like as follow:

measure 1 =

Var start_date = Calculate(Max(Table[startDate]),Filter(Table,[inclusion1]&&[inclusion2]&&[inclusion3]&&[inclusion4]&&[inclusion5]))

Var end_date = Calculate(Max(Table[endDate]),Filter(Table,[inclusion1]&&[inclusion2]&&[inclusion3]&&[inclusion4]&&[inclusion5]))

Return

Datediff(start_date,end_date,hour)

The above example is a simple example, but in some measure, I have 6 to 7 variables and writing same Filter() statement with every variable is cumbersome. Is there any other smart way of filtering the data within measure 

[Dunner2020]

@mahoneypat , I used following statement inside the measure to filter the data:

Var filter_data = 

FILTER(ALL('Table',Table[inclusion_col1],Table[inclusion_col2],Table[inclusion_col3]),[inclusion1]&&[inclusion2]&&[inclusion_col3])

When I tried to extract the column value of a particular column from the filter data variable using the following statement

Var start_date = Max(filter_data[startDate])

it greyed out [startDate].

Could you please help me out?

[mahoneypat · Accepted solution]

This is what I was suggesting, although I am not clear about your "inclusion" terms.  Is that shorthand?  What is the actual text for [inclusion1], [inclusion2], ...?

NewMeasure =
VAR filtertable =
    FILTER (
        ALL (
            Table[inclusion_col1],
            Table[inclusion_col2],
            Table[inclusion_col3],
            Table[inclusion_col4],
            Table[inclusion_col5]
        ),
        [inclusion1]
            && [inclusion2]
            && [inclusion3]
            && [inclusion4]
            && [inclusion5]
    )
VAR start_date =
    CALCULATE (
        MAX ( Table[startDate] ),
        filtertable
    )
VAR end_date =
    CALCULATE (
        MAX ( Table[endDate] ),
        filtertable
    )
RETURN
    DATEDIFF (
        start_date,
        end_date,
        HOUR
    )

Regards,

Pat

[mahoneypat]

You could first store your Filter table in a variable too, and then use it inside each CALCULATE in the other measures.  That way, you are only creating that table once.  Also, you should avoid Filtering the whole "Table".  Instead you should FILTER(ALL(Table[Column1], Table[Column2], ...) including only the columns on which you are evaluating inclusion criteria.

Regards,

Pat