Forum Discussion
DAX Calculated Column Help
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 |
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
3 Replies
Try:
Count ID =
CALCULATE(DISTINCTCOUNT(Table[ID), FILTER(Table, NOT(ISBLANK(Table[Date])) && Table[Date] <> DATE(2000, 1, 1)))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?
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
