Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
96 | |
79 | |
67 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |