Forum Discussion
Counting records based on two dates, second count must exclude records from the first
I was able to use this as a basis and got it working:
Edited Count =
CALCULATE(
COUNTROWS(FactTable),
USERELATIONSHIP(DateTable[Date], FactTable[EditedDate]),
NOT(FactTable[RecordID] IN VALUES(FactTable[RecordID]))
)I'm not sure how this is giving me the correct answer, but it must recognize the context that the two FactTable[RecordID]s are from different sets.
Super UserYou can use EXCEPT for that. Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
I'll take a look at how to use EXCEPT.
The data is very simple, I have a date dimension with sequential dates from 1950 - 2050. The fact table is only 4 columns and is laid out like below:
| FactID | AddDT | EditDT | RecordValue |
| 1 | 1/1/2024 | 6/15/2024 | ABC123 |
| 2 | 2/16/2024 | DEF456 | |
| 3 | 2/22/2024 | GHI789 | |
| 4 | 4/19/2024 | 6/16/2024 | JKL012 |
| 5 | 6/30/2024 | 8/1/2024 | MNO345 |
| 6 | 7/2/2024 | PQR678 | |
| 7 | 8/9/2024 | 10/31/2024 | STU901 |
| 8 | 10/31/2024 | VWX234 | |
| 9 | 11/1/2024 | YZZ567 | |
| 10 | 12/10/2024 | AAA890 |