DAX Keepfilter

[Dicken]

Hi,

I've seen an sqlbi vid where it seems to show the use of Keepfilters on two colunmns in the same table . I've not been able to get this to work
and have always used Filter wrapped in KF . so

 BandRed:=CALCULATE([TotalUnits],KEEPFILTERS(Table1[Color] = "Red" && Table1[Item] = "B" ))

Does not work even though the columns are in the same table but

 B and Red 2:=CALCULATE([TotalUnits] ,
                                                                             KEEPFILTERS(
                                                                                      FILTER(ALL(Table1[Color] , Table1[Item]),Table1[Color]= "Red" && Table1[Item] = "B" )) )

Does work, am I missing something as to the first simpler version, in the video is also seems to be used with summarize so I don't know if that makes a difference. Workbook attached any advice or help welcom.

RD

[romperstomper]

What is your issue with the first one? It works fine here - the same as the second.

[Dicken]

I've just downloaded the file i put up and it definitely shows an error for the first,

I re typed it just to try again and get the same error message from it.

 BandRed:=CALCULATE([TotalUnits],KEEPFILTERS(Table1[Color] = "Red" && Table1[Item] = "B" ))

RD

[romperstomper]

What error message? I loaded it into a pivot straight from your file with no changes and it produced the same results as the other measure.

[Dicken]

Sorry for not getting back to you, don't know? I definitely get an error, what I'll do is try in PBI and see if that's the same, I was coming to the conclusion in was a problem my end.

RD

[romperstomper]

Just tried it in 2016 which gives an error with the first one, with or without KEEPFILTERS. You need to use FILTER explicitly to use more than one column in the filter predicate.

[sandy666]

=CALCULATE([TotalUnits],KEEPFILTERS(Table1[Color] = "Red"&&Table1[Item] = "B" ))

Calculation error in measure 'Table1'[BandRed]: The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression.
Description and usage of KEEPFILTERS()

=CALCULATE([TotalUnits] ,

KEEPFILTERS(

 FILTER(ALL(Table1[Color] , Table1[Item]),Table1[Color]= "Red" && Table1[Item] = "B" )) )

No errors in formula.

[Dicken]

Not according to this

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

Wher Product 'color' and Product 'brand' are both used with an 'and' conjunction in the same Keepfilter argument.

[romperstomper]

I'm assuming it's a version issue, since my 2016 produces an error, but my 365 Insider doesn't. Since the two versions are internally the same, I don't really see the issue?

[Dicken]

I'm on 365, it works in PBi, thanks for looking into it, I might put a post on Mr Excel or MS website, see if it's just me or a version issue.
I'ts not a huge problem as I can get the result using the full Kf ( Fiter ( all , etc. It's just been annoying me.

Thanks again.

RD

[romperstomper]

It has to be a version issue given my findings and the fact it doesn't work on your 365 build.

[Dicken]

Yes, I know there are a few things that work in pbi that don't in pp i.e selectedvalue, mind you I find using If Hasonevalue values a bit more descriptive if what's acutely happening.

RD