Forum Discussion
count rows with some conditions
Super UserTry
Num occurrences =
SUMX(
ADDCOLUMNS(
SUMMARIZE( 'Table', 'Table'[idclient], 'Table'[idticket] ),
"num instances",
var firstInstance = CALCULATE( MIN( 'Table'[Date_time] ), REMOVEFILTERS( 'Date' ) )
RETURN CALCULATE( COUNTROWS( SUMMARIZE(
FILTER('Table', 'Table'[Date_time] > firstInstance ),
'Table'[idclient], 'Table'[idticket], 'Table'[Date_time]
) ) )
),
[num instances]
)
Thank you johnt75 , is it possible to show a table with the idclient, idticket and date_time for these occurrences?
- johnt75
I think you could add a calculated column as below, then filter out any entries which are 1
Is first entry =
var currentTicket = 'Table'[idticket]
var currentClient = 'Table'[idclient]
var currentDateTime = 'Table'[Date_time]
var numPrevEntries = CALCULATE( COUNTROWS('Table'), REMOVEFILTERS(), 'Table'[idclient] = currentClient && 'Table'[idticket] = currentTicket
&& 'Table'[Date_time] < currentDateTime )
return IF ( numPrevEntries = 0, 1, 0) Hi johnt75 actually as a column does not work because there is no row context I guess but if I write this way almost work. The only problem is that the duplicates are also 1 in this formula:
VAR result =COUNTROWS (FILTER (ALL('Table'),'Table'[idclient] = EARLIER ( 'Table'[idclient] )&& 'Table'[idticket] = EARLIER ( 'Table'[idticket] )&& 'Table'[Date_time] < EARLIER ( 'Table'[Date_time] )&& 'Table'[Date_time]<>blank()))RETURNIF ( result <> 0, 1, 0 )
Thank you! I am learning a lot with you 🙂- johnt75
I meant for you to use the calculated column so that you could display the data in a table, not to replace the num occurences measure.
By adding the new column as a filter the table will also obey any other filters or slicers you apply. e.g. using a slicer to select August produces
I know johnt75 , I didn't replace it. I used it as a filter. What I was trying to do is display the total for each client and the total for all in a table. That's why I said that if you tried to do with the calculated column the duplicates appear because they also have 1 in the condition.
- johnt75
You may be able to get rid of the duplicates if you replace the ALL('Table') with
CALCULATETABLE( SUMMARIZE( 'Table', 'Table'[idclient], 'Table'[idticket], 'Table'[Date_time]),
REMOVEFILTERS( 'Table' )
)