none
Multiple AND/OR filters in a Calculate expression

    Question

  • I would like to do the following using plain english:

    = CALCULATE([Measure], (dim_item[fielda] = "A" AND dim_item[fieldb] = "H")  OR dim_customer[field] = "X")

    I can do this in two formulas, but I thought there might be a way to do this in one.


    Thursday, November 14, 2013 6:13 AM

Answers

  • Have you tried this:

    =CALCULATE (
        [Measure],
        FILTER (
            CROSSJOIN (
                VALUES ( Dim_Item[FieldA] ),
                VALUES ( Dim_Item[FieldB] ),
                VALUES ( DIm_Customer[Field] )
            ),
            OR (
                AND (
                    Dim_Item[FieldA] = "A",
                    Dim_Item[FieldB] = "H"
                ),
                Dim_Customer[Field] = "X"
            )
        )
    )

    Depending on the number of distinct values in the various fields, you can try replacing the CROSSJOIN with a SUMMARIZE of the fact table and I suggest making some performance tests to choose the best option.


    Alberto Ferrari
    http://www.sqlbi.com


    Thursday, November 14, 2013 9:40 PM

All replies

  • Have you tried this:

    =CALCULATE (
        [Measure],
        FILTER (
            CROSSJOIN (
                VALUES ( Dim_Item[FieldA] ),
                VALUES ( Dim_Item[FieldB] ),
                VALUES ( DIm_Customer[Field] )
            ),
            OR (
                AND (
                    Dim_Item[FieldA] = "A",
                    Dim_Item[FieldB] = "H"
                ),
                Dim_Customer[Field] = "X"
            )
        )
    )

    Depending on the number of distinct values in the various fields, you can try replacing the CROSSJOIN with a SUMMARIZE of the fact table and I suggest making some performance tests to choose the best option.


    Alberto Ferrari
    http://www.sqlbi.com


    Thursday, November 14, 2013 9:40 PM
  • Sorry, I have not tested this yet.  So please wait on assigning the answer.  On the surface, doing a cross join would be 'expensive', and why I need some time to test this against just creating one formula for the 'AND', and another for the 'OR' and adding them together.

    Thank you.

    Tuesday, November 19, 2013 5:32 PM
  • Keep in mind that the solution of adding the two measures works if the measures are additive. For example, with DISTINCTCOUNTS, adding the results would produce incorrect numbers.

    If you plan to test, try even using SUMMARIZE ( Fact, DimItem[TestA], DimItem[TestB] ). Performance miught be very different depending on the distribution of your data


    Alberto Ferrari
    http://www.sqlbi.com

    Tuesday, November 19, 2013 5:39 PM