ISSUE FILTERING DATES IN CALCULATED COLUMN

[Arydperez]

There are 1 Fact Table and one linked Date Table (marked as Date Table). The link is a Single One in Date Table to Many in Fact Table.

I created a Calculated Column in the Fact Table that SHOULD show value 1 to the row that has the last Date (filtered by the slicer Date from the Date Table).

The problem is that the expression from the calculated column is always bringing the value 1  for the last date row of the group, REGARDLESS the value of the slicer. (I am attaching the PBIX):

Calculated Column expresson: 

last_date PRODUCT_WHS =

IF (

'FACT TABLE'[DATE]

= CALCULATE (

MAX ( ('FACT TABLE'[DATE] )),

ALLEXCEPT(

'FACT TABLE',

'FACT TABLE'[PRODUCT],

'FACT TABLE'[WHS]

)  ), 1, 0 )

Work for the whole range of dates:

Result is wrong when the slicer exclude the last date

Appreciate any idea to solve the Dax  Expression to make what it is suppose to do.

[tamerj1 · Accepted solution]

Hi @Arydperez 
You are are right it is simple but tricky at the same time. The thing that we did not consider in the comparison that Blank = Blank this is why the results kept populating 1 despite of the filter context. Please use the following and refer to the attached sample file.

last_date PRODUCT_WHS = 
VAR CurrentDate = MAX ( 'FACT TABLE'[DATE] )
VAR MaxtDate = 
    CALCULATE (
        MAX ( 'FACT TABLE'[DATE] ),
        REMOVEFILTERS ( 'FACT TABLE' ),
        VALUES ( 'FACT TABLE'[PRODUCT] ),
        VALUES ( 'FACT TABLE'[WHS] ),
        VALUES ( 'DATE TABLE'[Date] )
    )
VAR Result =
    IF ( NOT ISBLANK ( CurrentDate ) && CurrentDate = MaxtDate, 1 )
RETURN
    Result

[Arydperez]

Hi Tamer,

Thank you vm for the idea. Unfortunately it didn't work.  It brings only '0' and also, interestingly, brings the whole set of dates, regardless the slicer Date values. Result below.

[tamerj1]

@Arydperez 

Please try

last_date PRODUCT_WHS =
IF (
    MAX ( 'FACT TABLE'[DATE] )
        = CALCULATE (
            MAX ( 'FACT TABLE'[DATE] ),
            ALLSELECTED ( 'FACT TABLE'[Date] ),
            ALL ( 'FACT TABLE'[Value] )
        ),
    1
)

[Arydperez]

Hi,

It is much better!! Thanks! The problem is that it is still bringining all the data of the Fact Table even slicing it for a period it should leave records outside the view but also the values do not match.  If there is a way to share the pbix I could leave it. It is a very simple report.

[tamerj1]

Hi @Arydperez 

you can upload to onedrive, dropbox, WeTransfer or any similar cloud service and share the link in a reply. 

[tamerj1]

Hi @Arydperez 

a calculated shall work as it does not react dynamically with filter context. You have to add it as a measure 

last_date PRODUCT_WHS =
IF (
    MAX ( 'FACT TABLE'[DATE] )
        = CALCULATE (
            MAX ( 'FACT TABLE'[DATE] ),
            ALLEXCEPT ( 'FACT TABLE', 'FACT TABLE'[PRODUCT], 'FACT TABLE'[WHS] )
        ),
    1,
    0
)