locked
Re-Using FILTER() functions RRS feed

  • Question

  • Is there any way to reuse a FILTER() function across different measures besides copying and pasting it?

    I have created a series of time intelligence equivalents for a non-standard calendar, and I'd like to be able to capture each in a reusable format. My naive approach, which I assumed would fail, was to simply create a measure that contains only the FILTER() function. This obviously creates an error since FILTER() is returning a table, not a scalar.

    The behavior I'd like to get to is something like:

    CALCULATE( <arbitrary measure>, [MTDFilter]) where [MTDFilter] is a function that encapsulates the custom filter logic for my MTD filter (i.e. MTDFilter:= FILTER(...) - no actual measure contained within, only a FILTER()). When I use this format I get the error described on this page, which sort of makes sense, since the syntax checker is kicking in before the "measure" [MTDFilter] can be evaluated. The engine never evaluates [MTDFilter] to recognize that the function contained within is a table expression, which would be a legal value for the second argument of CALCULATE().

    My hunch is that what I'm asking is not possible. I don't think I've ever seen mention of creating user-defined functions in DAX, which is what I'd actually like to do, and it seems the errors are not surmountable in any manner similar to my naive approach detailed above. 

    I hope one of you will be able to tell me that I am wrong to be pessimistic and there's a way to do this.

    Friday, September 5, 2014 3:29 PM

Answers

  • Hi Greg,

    DAX Measures must return a scalar value and cannot return a table.
    what you could do is to creaet a calculated measure which includes the filter
    BaseMeasure:=SUM('tabe'[Value])
    BaseMeasureFiltered:=CALCULATE([BaseMeasure], FILTER(...))

    for all subsequent calculations you can use [BaseMeasureFiltered] instead of [BaseMeasure] to use the same filter several times

    hth,
    -gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    • Marked as answer by greggyb Friday, October 3, 2014 2:43 PM
    Friday, October 3, 2014 2:32 PM
    Answerer
  • Thanks Gerhard. This isn't quite what I'd like to do.

    I really want to be able to define my YTD (as an example) logic only once, and then refer to that somehow in every measure that needs to be evaluated YTD, rather than copy and paste FILTER()s.

    To sketch it out, I'd like to create this somewhere:

    YTD:=
    FILTER( ALL( Calendar )
        , Calendar[Year] = MAX( Calendar[Year] )
            && Calendar[Date] <= MAX( Calendar[Date] )
    )

    Then in every measure that is to be evaluated YTD I could write simply:

    Measure1 YTD:=
    CALCULATE( [Measure1]
        , [YTD]
    )
    
    ...
    
    MeasureN YTD:=
    CALCULATE( [MeasureN]
        , [YTD]
    )
    

    This example is pretty trivial, but I've run into situations where I have more gnarly FILTER() functions, and refactoring gets annoying when I've got a dozen measures all sharing the same filter logic and I have to manually change each.

    Thank you for confirming that a measure is strictly required to return a scalar, which would make the exact process I've laid out impossible.

    I'll mark this as closed. I have opened a Connect item with this as a suggestion for anyone who may be interested in similar functionality.


    • Edited by greggyb Friday, October 3, 2014 3:03 PM connect link
    • Marked as answer by greggyb Friday, October 3, 2014 3:03 PM
    Friday, October 3, 2014 2:43 PM

All replies

  • Greg, is this still an issue?

    Thanks!


    Ed Price, Azure & Power BI Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Wednesday, October 1, 2014 11:41 PM
  • Not so much an issue as a point of curiosity/hoped for feature.
    Thursday, October 2, 2014 2:54 PM
  • Hi Greg,

    DAX Measures must return a scalar value and cannot return a table.
    what you could do is to creaet a calculated measure which includes the filter
    BaseMeasure:=SUM('tabe'[Value])
    BaseMeasureFiltered:=CALCULATE([BaseMeasure], FILTER(...))

    for all subsequent calculations you can use [BaseMeasureFiltered] instead of [BaseMeasure] to use the same filter several times

    hth,
    -gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    • Marked as answer by greggyb Friday, October 3, 2014 2:43 PM
    Friday, October 3, 2014 2:32 PM
    Answerer
  • Thanks Gerhard. This isn't quite what I'd like to do.

    I really want to be able to define my YTD (as an example) logic only once, and then refer to that somehow in every measure that needs to be evaluated YTD, rather than copy and paste FILTER()s.

    To sketch it out, I'd like to create this somewhere:

    YTD:=
    FILTER( ALL( Calendar )
        , Calendar[Year] = MAX( Calendar[Year] )
            && Calendar[Date] <= MAX( Calendar[Date] )
    )

    Then in every measure that is to be evaluated YTD I could write simply:

    Measure1 YTD:=
    CALCULATE( [Measure1]
        , [YTD]
    )
    
    ...
    
    MeasureN YTD:=
    CALCULATE( [MeasureN]
        , [YTD]
    )
    

    This example is pretty trivial, but I've run into situations where I have more gnarly FILTER() functions, and refactoring gets annoying when I've got a dozen measures all sharing the same filter logic and I have to manually change each.

    Thank you for confirming that a measure is strictly required to return a scalar, which would make the exact process I've laid out impossible.

    I'll mark this as closed. I have opened a Connect item with this as a suggestion for anyone who may be interested in similar functionality.


    • Edited by greggyb Friday, October 3, 2014 3:03 PM connect link
    • Marked as answer by greggyb Friday, October 3, 2014 3:03 PM
    Friday, October 3, 2014 2:43 PM
  • in the simple case of YTD you may also use TOTALYTD()-fucntion

    but i guess your scenario is a bit more complex and you would have come up with the TOTALYTD function on your own if it would really help 


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    Monday, October 6, 2014 7:02 AM
    Answerer
  • I use YTD only for an easily understandable example. and TOTALYTD() only covers the case of a 365 day year. Any non-standard fiscal calendar will require custom filtering logic.

    In terms of self-service BI where a Power Pivot model is published for end users to customize and use for their own purposes, it would be great if we could encapsulate the trickier parts, such as proper filter logic, into a set of easily referenceable measures it would greatly increase the end users' ability to glean meaningful insight.

    Monday, October 6, 2014 2:34 PM