DAX Keepfilter

[dicken]

Hi,
Cross posted Excel Forum ;

https://www.excelforum.com/office-365/1399068-dax-filtering-between-dates-with-keepfilter.html#post5787380https://www.excelforum.com/office-365/1399068-dax-filtering-between-dates-with-keepfilter.html#post5787380

Can someone help with this I have a Keepfilter measure;

CALCULATE([TotalUnits],KEEPFILTERS( dDateT[Date] >= DATE( 2020,3,1) && dDateT[Date] <= DATE(2020,7,31) ))

but I would like to take the dates from another table, not use DATE( ) ,

CALCULATE([TotalUnits],KEEPFILTERS( dDateT[Date] >= MAX(pdate[StardD]) && dDateT[Date] <= MAX(pdate[EndD] ) ))

and while this give the correct overall total, when dropped into a table or matrix it does not 'see' the dates there, it's as if I'd just used filter.
I have got aroung this by using the following;

Bwrwn Pdates =
CALCULATE (
    [TotalUnits],
    KEEPFILTERS (
        FILTER (
            ALL ( dDateT[Date] ),
            dDateT[Date] <= MAX ( Pdate[EndDate] )
                && dDateT[Date] >= MAX ( Pdate[Sdate] )
        )
    )
)

This works but I don't know why the other doesn't can anyone help?
Richard.

 

[Matt Allington]

Keepfilters is clashing with ALL in this formula; they do the opposite thing.
as for why it doesn’t work, it’s impossible to say without you sharing the visual, and also explaining which columns are providing filter context, and what the model looks like.

 

[dicken]

Keepfilters is clashing with ALL in this formula; they do the opposite thing.
as for why it doesn’t work, it’s impossible to say without you sharing the visual, and also explaining which columns are providing filter context, and what the model looks like.

When you say keepfilters is clashing with ALL, in the first example I'm not use ALL, the tables are one fact table, date and sales,
then a dates to filter between table two columns start and end, and then the calendar table,
as I said above keepfiltes works when I just use DATE() to give the dates I want but if I take them from the table using MAX, which I believe should then work as a scaler value, the correct total is returned but I get it for each row, I have attached the source excel file at excel forum,
when you say share a visual in what form?

Richard.

 

[dicken]

Keepfilters is clashing with ALL in this formula; they do the opposite thing.
as for why it doesn’t work, it’s impossible to say without you sharing the visual, and also explaining which columns are providing filter context, and what the model looks like.

Screen shot included.
I've recreated the scenario ; The first uses, this works fine;

CALCULATE([TotalUnits],KEEPFILTERS(Ddate[Date] >= DATE(2002,2,1) && Ddate[Date] <= DATE(2002,2,10) ))

The second uses , this has two problems;

CALCULATE([TotalUnits],KEEPFILTERS(Ddate[Date]<=MAX(dimTest[ EndD ]) && Ddate[Date]>=MAX(dimTest[StartD]) )) )

Which sees the Start but not the end date, but does not see the external 'AND' operation I thought keepfilters did,

The Third uses ; this has one problem;

CALCULATE(
    [TotalUnits],KEEPFILTERS( FILTER(ALL(Ddate[Date]),Ddate[Date]<= MAX(dimTest[ EndD ]) && Ddate[Date]>= MAX(dimTest[StartD]) )) ) )

Which does see the external date, the AND part but again, does not recognize the End date, do,
So two problems, one regarding the KF and also not seeing the End date, the table for the two dates is not connected,
do I need to use USERELATIONSHIP and link both start and end dates fact table?

Richard

 

[Matt Allington]

Please post a screen shot of the "Manage relationships" list from your model.

 

[dicken]

Hi,

This was how I originally had it, but as I was only using the date as a values I then disconnected it, but what I did was after watch
something on sqlbi, decided to use the values in a variable in it seem to work, so as seen below
this works

KFvar = 
VAR mind = MAX(Table2[StartD]) 
VAR maaxd = MAX(Table2[EndD ]) 
RETURN 
CALCULATE([TotalUnits],KEEPFILTERS(dDate[Date] <= maaxd && dDate[Date] >= mind ) )

but does not, or at least does the see the external filter context,

Direct = IF([TotalUnits],  
                                CALCULATE([TotalUnits],KEEPFILTERS(dDate[Date] >= MAX(Table2[StartD])
                                                      && dDate[Date] <= MAX(Table2[EndD ]) )))

But using filter and then wrapping in KF and it's all works again?

KFF :=
CALCULATE (
    [TotalUnits],
    KEEPFILTERS (
        FILTER (
            ALL ( dDate[Date] ),
            dDate[Date] <= MAX ( Table2[EndD ] )
                && dDate[Date] >= MAX ( Table2[StartD] )
        )
    )
)

Richard.