Count distinct start dates by item

[Anonymous]

Dear all, 

I have an issue with counting all distinct start dates per item (id). 

f.e. of the data 

ID	date	start or stop date
1	12/02/2024	start
1	14/02/2024	stop
1	15/02/2024	start
1	16/02/2024	stop
2	05/03/2024	start
2	05/03/2024	stop
2	05/03/2024	start
2	05/03/2024	stop
3	10/06/2024	start
3	10/06/2024	stop
4	10/06/2024	start
4	10/06/2024	stop

i am trying to make a table that has following info (only the start dates per item - and total amount of start dates that each item has) 

ID	test date	start or stop date	count amount of days tested
1	12/02/2024	start	2
1	15/02/2024	start	2
2	05/03/2024	start	1
3	10/06/2024	start	1
4	10/06/2024	start	1

I dont really need to start or stop column but i kept it here as en example. 

now i only have "1" everytime i try to calculate how many distinct dates there are, but if i remove the date column it does calculate correctly the distinctdates, with filter that start stop column = start. 

Count amount of days tested = 

CALCULATE (
DISTINCTCOUNT (
'table'[test date] ) , 
FILTER ('table', 'table'[start or stop] = "start") ) 

Afterwards I want to be able to make a percentage with all the ID that have more then 1 (or only 1 test date) test date over ( / ) all ID in total. So i can see a percentage of how many ID were only tested once.  

how should change this calculation? 

 kind regards 

[Anonymous]

Hi  @Anonymous ,

You can try below steps to create calculated column and calculated table to get result:

StartDatesOnly = 
FILTER(
    'table',
    'table'[start or stop date] = "start"
)

Count of Start Dates = 
CALCULATE(
    DISTINCTCOUNT('StartDatesOnly'[date]),
    ALLEXCEPT('StartDatesOnly', 'StartDatesOnly'[ID])
)

FinalTable = 
SUMMARIZE(
    'StartDatesOnly',
    'StartDatesOnly'[ID],
    'StartDatesOnly'[date],
    "count amount of days tested", COUNT(StartDatesOnly[Count of Start Dates])
)

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

I have following error with the calculated column: 

StartDatesOnly = 
FILTER(
    'table',
    'table'[start or stop date] = "start"
)

The Expression refers to multiple columns, multiple columns cannot be converted to a scalar value. 

the start and stop column is a column i made using the first charachters of another column which had a whole start stop sentence. could that be the problem? do i need to refer to the original column? 

[Anonymous]

I am quite new to this forum ... i unfortunately cant share the power bi since it has confidential information and is a big report with mutiple tabs and views of data. Do you need more info?