Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Count distinct start dates by item

Dear all, 

 

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

 

f.e. of the data 

ID datestart or stop date
112/02/2024start
114/02/2024stop 
1 15/02/2024start
116/02/2024stop
205/03/2024start
205/03/2024stop 
205/03/2024start
205/03/2024stop 
310/06/2024start
310/06/2024stop 
410/06/2024start
410/06/2024stop 

 

 

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 datestart or stop datecount amount of days tested
112/02/2024start2
1 15/02/2024start2
205/03/2024start1
310/06/2024start1
410/06/2024start1

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 

 

CALCULATE
dates
distinctcount

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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?

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      Anonymous
      Not applicable
      2 years ago

      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?