Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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
Proud to be a Super User!
Paul on Linkedin.
Try:
Count ID =
CALCULATE(DISTINCTCOUNT(Table[ID), FILTER(Table, NOT(ISBLANK(Table[Date])) && Table[Date] <> DATE(2000, 1, 1)))
Proud to be a Super User!
Paul on Linkedin.
@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
Proud to be a Super User!
Paul on Linkedin.