locked
OR filtering RRS feed

  • Question

  • hi eb

    How can i implement the following filter over power pivot\query in excel?

    (billStatus = 0 and terminationDate = 0)

    OR

    billStatus in (some selection of values).

    This filter is not defined on model's data source or query.

    but it is used by user's in excel pivot table or just table who uses the power pivot\query as its source.

    Thanks for your thoughts!!!

    Rea


    Monday, March 31, 2014 12:08 PM

Answers

  • I compiled a little sample that may be useful for you - you can download it from http://files.gbrueckl.at/public/OR.xlsx

    it basically uses this kind of calculation:

    OR :=
    CALCULATE (
        SUM ( Facts[Value] ),
        FILTER (
            'Facts',
            (
                CONTAINS (
                    VALUES ( 'Status'[Status] ),
                    'Status'[Status], Facts[Status]
                )
                    && CONTAINS (
                        VALUES ( 'Date'[Date] ),
                        'Date'[Date], Facts[Date]
                    )
            )
                || (
                    CONTAINS (
                        VALUES ( 'Status2'[Status2] ),
                        'Status2'[Status2], Facts[Status]
                    )
                        && CONTAINS (
                            VALUES ( 'Date2'[Date2] ),
                            'Date2'[Date2], Facts[Date]
                        )
                )
        )
    )
    

    selections of slicer 'Date' and 'Status' / 'Date2' and 'Status2' are combined using AND, whereas both sets of slicers are combined using OR

    thats the most flexible and feasible solution i could think of

    hth,
    gerhard


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

    • Proposed as answer by Michael Amadi Wednesday, April 23, 2014 7:52 AM
    • Marked as answer by reapeleg Wednesday, April 30, 2014 7:16 AM
    Tuesday, April 22, 2014 12:49 PM
    Answerer

All replies

  • Hi,

    Check the below link. Hope you are looking on similar terms.

    http://javierguillen.wordpress.com/2012/07/30/creating-lookup-tables-in-powerpivot-using-sql-queries/


    Regards, PS

    Monday, March 31, 2014 1:20 PM
  • Hey reapeleg,

    I think you will get some more infos if you share your Power Pivot Model (if its possible) or try to translate it to Adventure Works. It's very hard to follow what you are looking for.

    Does this help you? http://www.daxpatterns.com/parameter-table/

    I hope I was of some help!

    ___________________________________

    Kind regards, please mark helpful posts! :)

    Tuesday, April 1, 2014 2:57 PM
  • thanks

    my problem is actually a general filtering issue in excel.

    Say you have a table with 3 columns:date,status and value.

    And you want to filter rows with an OR condition:

    columns date and status are 0

    OR

    column status is 1 (date column is not filtered!).

    I dont want to add a calculated column whith an iif() statement that will set

    true or false for every row because this is a very static solution that solves only one

    case of combination of values from the date and status columns.

    Hope i was able to make it more cleare now.

    Thanks!

    Rea

    Wednesday, April 2, 2014 5:13 AM
  • Any new ideas for Rea?

    Thanks!


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

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

    Monday, April 21, 2014 5:07 AM
  • I compiled a little sample that may be useful for you - you can download it from http://files.gbrueckl.at/public/OR.xlsx

    it basically uses this kind of calculation:

    OR :=
    CALCULATE (
        SUM ( Facts[Value] ),
        FILTER (
            'Facts',
            (
                CONTAINS (
                    VALUES ( 'Status'[Status] ),
                    'Status'[Status], Facts[Status]
                )
                    && CONTAINS (
                        VALUES ( 'Date'[Date] ),
                        'Date'[Date], Facts[Date]
                    )
            )
                || (
                    CONTAINS (
                        VALUES ( 'Status2'[Status2] ),
                        'Status2'[Status2], Facts[Status]
                    )
                        && CONTAINS (
                            VALUES ( 'Date2'[Date2] ),
                            'Date2'[Date2], Facts[Date]
                        )
                )
        )
    )
    

    selections of slicer 'Date' and 'Status' / 'Date2' and 'Status2' are combined using AND, whereas both sets of slicers are combined using OR

    thats the most flexible and feasible solution i could think of

    hth,
    gerhard


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

    • Proposed as answer by Michael Amadi Wednesday, April 23, 2014 7:52 AM
    • Marked as answer by reapeleg Wednesday, April 30, 2014 7:16 AM
    Tuesday, April 22, 2014 12:49 PM
    Answerer