AllExcept and KeepFilters

[msa_007]

Hello,

I'm quite simply struggling to understand why the "Received Date" part of this DAX summary table is not obeying the AllExcept function and return the same date for each document record - I suspect a context issue as well as something to do with the KeepFilter function as it works when the KeepFilter function is removed.  However, I need to exclude the review status<>"Rejected" from the document register (note the sent date is being returned from the Transmittal table.

The model is setup based upon 2 fact tables (Document Register and Transmittal), and various dimension tables (Project, Document, Revision, Doc Status) all related to the fact tables.

Would appreciate any help to understand this issue.  Thanks.

Summary - CDS = 
    var Table1 =        
            ADDCOLUMNS(                
                SUMMARIZE(                                                                      --table with the core columns required
                    'Document Register',                        
                    'Document Register'[ProjectID],                        
                    'Document Register'[ProjectID_DocumentNo],                    
                    'Document Register'[Contract (ID)],
                    'Document Register'[Discipline Name (ID)],
                    'Document Register'[Sub Project (ID)],
                    'Document Register'[Sender Company],
                    'Document Register'[Project Phase (ID)],
                    "Received Date",                                                           --return the earliest transmittal sent date                        
                        CALCULATE(
                            MIN(Transmittal[Date Sent]),
                            ALL(Revision),
                            ALL('Doc Status'),
                            'Doc Status'[Status Code]<>"PLA",
                            KEEPFILTERS(                                                        --retain any existing filters on the document register table and add this filter
                                FILTER(
                                    'Document Register',
                                    'Document Register'[Review Status]<>"Rejected"
                                )
                            ),
                            ALLEXCEPT(
                                'Document Register',
                                'Document Register'[ProjectID_DocumentNo]
                            )
                        )                  
                ),                
                "Planned Date",                                                                 --Calculated planned date
                    CALCULATE(
                        MAX( 'Document Register'[Planned Date] ),                    
                        'Document Register'[Latest Revision] = "Latest",
                        ALLEXCEPT(
                            'Document Register',
                            'Document Register'[ProjectID_DocumentNo]                        
                        )
                    ),
                "Forecast Date",                                                                --Calculated forecast date
                    CALCULATE(
                        MAX( 'Document Register'[Forecast Date] ),                    
                        'Document Register'[Latest Revision] = "Latest",
                        ALLEXCEPT(
                            'Document Register',
                            'Document Register'[ProjectID_DocumentNo]                        
                        )
                    ),       
                "Count RevCode Numeric",                                
                    CALCULATE(
                        DISTINCTCOUNT('Document Register'[ProjectID_DocumentNo]),                    
                        Revision[Is RevCode Numeric] = true,                                    --if rev code is a number - couple of conditions exist
                        ALLEXCEPT(
                            'Document Register',
                            'Document Register'[ProjectID_DocumentNo]                        
                        )
                    )
            )        
    RETURN
Table1

[msa_007]

Thanks for your reply @bhanu_gautam 

I tried this and it has now lost all its context.

Ultimately, I'm trying to make the table look like this.  Currently, the bold received date is not showing for the same document "123456".

Planned Date	Forecast Date	Received Date	Count RevCode Numeric	Contract (ID)	Discipline Name (ID)	Sub Project (ID)	Sender Company	Project Phase (ID)	ProjectID	ProjectID_DocumentNo
		10/03/2023 0:00	1	General	General (G)		Bob's Factory	Execution (EXE)	A0000	123456
		10/03/2023 0:00	1	General	General (G)	Execution (EXE)	Bob's Factory	Execution (EXE)	A0000	123456

[bhanu_gautam]

@msa_007 , Try updated measure

Summary - CDS =
VAR ReceivedDate =
CALCULATE(
MIN(Transmittal[Date Sent]),
REMOVEFILTERS(Revision),
REMOVEFILTERS('Doc Status'),
'Doc Status'[Status Code] <> "PLA",
FILTER(
'Document Register',
'Document Register'[Review Status] <> "Rejected"
),
ALLEXCEPT(
'Document Register',
'Document Register'[ProjectID_DocumentNo]
)
)
VAR PlannedDate =
CALCULATE(
MAX('Document Register'[Planned Date]),
'Document Register'[Latest Revision] = "Latest",
ALLEXCEPT(
'Document Register',
'Document Register'[ProjectID_DocumentNo]
)
)
VAR ForecastDate =
CALCULATE(
MAX('Document Register'[Forecast Date]),
'Document Register'[Latest Revision] = "Latest",
ALLEXCEPT(
'Document Register',
'Document Register'[ProjectID_DocumentNo]
)
)
VAR CountRevCodeNumeric =
CALCULATE(
DISTINCTCOUNT('Document Register'[ProjectID_DocumentNo]),
Revision[Is RevCode Numeric] = TRUE,
ALLEXCEPT(
'Document Register',
'Document Register'[ProjectID_DocumentNo]
)
)
RETURN
ADDCOLUMNS(
SUMMARIZE(
'Document Register',
'Document Register'[ProjectID],
'Document Register'[ProjectID_DocumentNo],
'Document Register'[Contract (ID)],
'Document Register'[Discipline Name (ID)],
'Document Register'[Sub Project (ID)],
'Document Register'[Sender Company],
'Document Register'[Project Phase (ID)]
),
"Received Date", ReceivedDate,
"Planned Date", PlannedDate,
"Forecast Date", ForecastDate,
"Count RevCode Numeric", CountRevCodeNumeric
)