DAX Keepfilters on two tables,

[dicken]

Hello,
I'd like to run to filter my result by two tables but using keepfilter so the external filter context is kept,

<
EVALUATE
FILTER (
CROSSJOIN ( ALL ( 'Product'[Color] ), ALL ( Sales[Sales] ) ),
'Product'[Color] = "Red"
&& Sales[Sales] > 5
) >
This returns the correct answer but I get it repeated where the color isn't red, so for blue green etc. I've tried replacing filter with KF and nesting Filer within
Keepfilter, which works if you are just using different columns from the same table. Any suggestions,

Richard.

 

[Matt Allington]

I’m just trying to get my head around what you are doing. Why are you using crossjoin? What are you trying to do? Do you have a relationship between Product and Sales? Why are you writing this as a DAX query?

 

[dicken]

the query is to filter the sales by sales amount on one table and color on another, as All can't take two tables, I fed them to filter via crossjoin, creating one table with the two columns to be filtered, but couldn't get the Keepfilter part to work , which I needed so that the external "AND" filter context was respected.

To cut a long story short I found the solution on SQLBI,
Red & 10 :=
CALCULATE (
[Total Revenue],
KEEPFILTERS (
FILTER (
CROSSJOIN ( ALL ( 'Product'[Color] ), ALL ( Sales[Sales] ) ),
'Product'[Color] = "Red"
&& Sales[Sales] = 10
)
)
)
Column H wrapped in Keepfilter as opposed to just using filter;

I hope this explains what I was trying to do.
Richard

 

[Matt Allington]

Red & 10 :=
CALCULATE (
[Total Revenue],
KEEPFILTERS ( 'Product'[Color] = "Red" ),
KEEPFILTERS ( Sales[Sales] = 10 )
)

it assumes there is a relationship between Product and Sales.

 

[dicken]

Yes, there's a product Id linking them,

Thanks,
Richard.

 

[Matt Allington]

The relationship removes the need for the crossjoin because the tables are already "joined".

 

[dicken]

So are you saying you would just use two 'Keepfilters', which is what I have done in the past?
RD

 

[Matt Allington]

Yes, that's right. You need one for each table/filter parameter

 

[dicken]

Yes, that's right. You need one for each table/filter parameter

I've just been trying to get to a best practice way of writing things so as to understand what's happening. As you point out in supercharge
CALCULATE, "FILTER" is just sugar for FILTER( ALL etc.
I first came across using the crossjoin in SQLBI specifying multiple filter

https://www.youtube.com/watch?v=jyxZxFT_pAY&t=205s

One thing two KF's don't seem to cope with an OR or || ,
but I have used

Red Or Sales 6 :=
CALCULATE (
    [Gross Sales],
    KEEPFILTERS (
        FILTER (
            CROSSJOIN ( ALL ( 'Product'[Color] ), ALL ( Sales[Sales] ) ),
            'Product'[Color] = "Red"
                || Sales[Sales] > 12
        )
    )
)

Admittedly I have not thoroughly tested this.

Richard.