Forum Discussion
AllExcept and KeepFilters
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
2 Replies
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
)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
