Forum Discussion
Distinct count of multiple columns created from the same record
Community SupportHi Frank225 ,
Try to create a calculated column like this:
Column 2 =
VAR max_date =
CALCULATE (
MAX ( 'Table (2)'[EndDate] ),
ALLEXCEPT ( 'Table (2)', 'Table (2)'[StartDate], 'Table (2)'[TruckID] )
)
RETURN
IF (
'Table (2)'[EndDate] <> max_date,
0,
'Table (2)'[EndDate].[Day] - 'Table (2)'[StartDate].[Day] + 1
)Sample .pbix has been updated.
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I have tested it on my dataset and I believe it is very close to the solution now. With increasing records for truck A you see all jobs done on the same day ('Column' = 1) are correct now. If 'Column' > 1 it still seems that no correction for overlapping dates is in place. For example:
3-1-2020 till 5-1-2020 = 3
5-1-2020 till 9-1-2020 = 5
9-1-2020 till 10-1-2020 = 2
This should be in total 8 days instead of 10. --> 3-1-2020 till 10-1-2020 is 8 days.
Thanks in advance!
- V-lianl-msft
Hi Frank225 ,
Could you share the sample pbix via cloud service like onedrive for business?
Please mask any sensitive data before uploading.
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.