DAX Calculated Column Help

[Shree_185]

Hi Everyone, 

Trying to replicate the below view in one line query (calculated column)

Calculate column = Region-wise Distinct Count ID  WHERE Date is not blank OR Date is not equal to 01/01/2000

Sample data :

To copy-paste sample data in Excel

Region	ID	Date
CGC	S001934-A-001	1/1/2000
CGC	S001935-A-001	3/29/2021
CGC	S002020-A-002	10/18/2021
CGC	S002027-A-002	9/1/2021
CGC	S002033-A-002	9/21/2021
CGC	S002034-A-002	1/1/2000
CTX	S000709-A-001
CTX	S001129-A-001	6/3/2021
CTX	S001136-A-002	3/9/2022
CTX	S001140-A-001	6/22/2022
OPW	S000761-A-001	1/1/2000
OPW	S000773-A-001
OPW	S000775-A-001	8/17/2021
OPW	S000784-A-001	8/27/2021
OPW	S000830-A-001	7/23/2022

[PaulDBrown]

Try:

Count ID =
CALCULATE(DISTINCTCOUNT(Table[ID), FILTER(Table, NOT(ISBLANK(Table[Date])) && Table[Date] <> DATE(2000, 1, 1)))

[Shree_185]

@PaulDBrown Thanks for your help. The logic didn't work? or maybe I did some mistake. can you please send me the PBIX file if possible?

[PaulDBrown · Accepted solution]

If you need the visual you posted, it should work. If you need it in a table visual, you need to add an ALLEXCEPT function in the filter expression:

Count ID ALLEXCEPT =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[ID] ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[Region] ),
        NOT ( ISBLANK ( 'Table'[Date] ) )
            && 'Table'[Date] <> DATE ( 2000, 1, 1 )
    )
)

I've attached the sample PBIX file