Forum Discussion
count rows with some conditions
Thank you again for your answer! There is another problem that I forgot to mention. When you select a filter like for instance month, in your formula the first result is not considered even it is the same occurrence from the previous month.
About your new formula, the result for idclient 001 is 55 and should be 10 and If you filter august should be 5. PBi file https://1drv.ms/u/s!AuMLcKZkL7PFgkY3qjc2GJ35giM2?e=MAcPtQ
thank you again for your time.
Can you confirm what the date filter is supposed to filter out. You say that August should return 5, so it looks like you want to find the first occurence of a ticket / client combination regardless of when it occurred and then find the number of repeat occurences within the selected time frame ?
- johnt754 years ago
Super User
Try
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]
) - Anonymous4 years agoNot applicable
yes, that's right. I need to find repeated occurrences for the same idclient and ticket excluding the duplicate values)
- Anonymous4 years agoNot applicable
Thank you johnt75 , is it possible to show a table with the idclient, idticket and date_time for these occurrences?
- johnt754 years ago
Super User
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) - Anonymous4 years agoNot applicable
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 🙂 - johnt754 years ago
Super User
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
- Anonymous4 years agoNot applicable
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.
- johnt754 years ago
Super User
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' )
)