SummarizeColumns Query with Filters Or/And

[gooranga1]

I have a SummarizeColumns query which works fine but I would like to have and or logic in the Filter on 2 different dimensions. What I am trying to do is to have the logic where (tracking prefix = 016162 and person id = 5489552) or (tracking prefix = 016835). I could do it in 2 queries and merge them but I was trying to work out how to do it in one go. Any ideas?

EVALUATE
SUMMARIZECOLUMNS (
    'Tracking Prefix'[Tracking Prefix],
    Route[DC Id],
    Company[Company Id],
    FILTER (
        ALL ( Route[DC Id] ),
        CONTAINSROW (
            //{ 191 },
            { 95, 89, 92, 96, 94, 90, 91, 321, 93, 191, 2180, 2575, 180 },
            Route[DC Id]
        )
    ),
    FILTER ( Company, Company[Company Id] = 3222 ),
    FILTER (
        ALL ( 'Tracking Prefix' ),
        CONTAINSROW ( { "016162","016835" }, 'Tracking Prefix'[Tracking Prefix] )
    )
    , FILTER ( Engineers, Engineers[Person Id] = 5489552 ),

    FILTER (
        'Date',
        'Date'[Date]
            >= DATE ( YEAR ( NOW () ) - 2, 01, 01 )
            && 'Date'[Date]
                < DATE ( YEAR ( NOW () ), MONTH ( NOW () ), DAY ( NOW () ) ) --&& 'Date'[Current Week] <> "CurrentWeekByBox"
    ),
    "Total Ops Deliveries", [Ops Total Deliveries],

)

[OwenAuger · Accepted solution]

Hi @gooranga1 

You could use some examples from this page, particularly examples #12 or #13:

https://www.sqlbi.com/articles/filter-arguments-in-calculate/

Using example #12, the DAX for that particular filter could look something like:

FILTER (
	CROSSJOIN (
		ALL ( 'Tracking Prefix'[Tracking Prefix] ),
		ALL ( Engineers[Person Id] )
	),
	'Tracking Prefix'[Tracking Prefix]
		IN { "016162", "016835" }
		|| Engineers[Person Id] = 5489552
)

Regards,

Owen

[gooranga1]

Thanks @OwenAuger  that's perfect. I had to replace the tables in the cross join with filtered calculate tables as both the tables are very large so the query took too long!

Thanks!