topic Re: Count distinct start dates by item in DAX Commands and Tips

Count distinct start dates by item

Anonymous — Wed, 10 Jul 2024 12:12:45 GMT

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

Re: Count distinct start dates by item

Anonymous — Wed, 10 Jul 2024 12:20:32 GMT

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?

Re: Count distinct start dates by item

Anonymous — Fri, 12 Jul 2024 07:50:38 GMT

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.

Re: Count distinct start dates by item

Anonymous — Tue, 16 Jul 2024 08:00:10 GMT

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?