count rows with some conditions

[maryjanesmith]

Hi everyone!

I need to count the rows with same idticket, idclient after the first time that they appear. I also have to exclude duplicates so I only have to count once these records. The result is indicated below. so the total in this example should be 10. Can you help, please?
PBI file https://1drv.ms/u/s!AuMLcKZkL7PFgkY3qjc2GJ35giM2?e=aCetar

thank you!

[maryjanesmith]

Hi @Jihwan_Kim , the problem is that my real data has many duplicate dates like the table from the first post, with your formula they are accounted. Do you know how to remove the duplicates? thank  you very much for your help.

[johnt75]

Num occurrences = 
var currentClient = SELECTEDVALUE( 'Table'[idclient] )
var currentTicket = SELECTEDVALUE( 'Table'[idticket] )
var minDate = CALCULATE( MIN('Table'[Date_time]), 'Table'[idclient] = currentClient && 'Table'[idticket] = currentTicket )
var result = COUNTROWS( SUMMARIZE( 
FILTER( 'Table', 'Table'[idclient] = currentClient && 'Table'[idticket] = currentTicket && 'Table'[Date_time] > minDate ),
'Table'[idclient], 'Table'[idticket], 'Table'[Date_time]
) )
return result 

using SUMMARIZE across the idclient, idticket and Date_Time columns will filter out any duplicates

[maryjanesmith]

Hi @johnt75 ,

thank you very much answer but the measure result is blank, did you tested?

[johnt75]

Yes, if I put that measure on a table or matrix visual with idticket and idclient on the rows then I get correct values.

Do you have any other filters applied which could restrict the results ?

[maryjanesmith]

No, I don't have any filters that restrict the results. I need this measure on a card but the result of that measure is blank. In the matrix the total that you see is blank and for that reason on the card is blank too.

In the future, I will only have a filter that is in the date table "interval" to see which tickets belong to each interval.

Your solution seems so elegant and nice but for the moment doesn't work for this purpose :/. 

[johnt75]

I understand, you want the overall total not broken down by client / ticket.

Num occurrences = SUMX( ADDCOLUMNS( SUMMARIZE( 'Table', 'Table'[idclient], 'Table'[idticket], 'Table'[Date_time] ),
"@value",
var currentClient = CALCULATE( SELECTEDVALUE( 'Table'[idclient] ) )
var currentTicket = CALCULATE( SELECTEDVALUE( 'Table'[idticket] ) )
var minDate = CALCULATE( MIN('Table'[Date_time]), 'Table'[idclient] = currentClient && 'Table'[idticket] = currentTicket )
var result = COUNTROWS( SUMMARIZE( 
FILTER( 'Table', 'Table'[idclient] = currentClient && 'Table'[idticket] = currentTicket && 'Table'[Date_time] > minDate ),
'Table'[idclient], 'Table'[idticket], 'Table'[Date_time]
) )
return result 
), [@value])

should give you what you're after

[maryjanesmith]

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.

[johnt75]

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 ?

[maryjanesmith]

yes, that's right. I need to find repeated occurrences for the same idclient and ticket excluding the duplicate values)

[johnt75]

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]
)

[maryjanesmith]

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)

[maryjanesmith]

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()

)

)

RETURN

IF ( 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

[maryjanesmith]

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' )
)

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

It is for creating a measure.

test 2 fix =
SUMX (
'Table',
CALCULATE (
SWITCH (
TRUE (),
VAR currentidticket =
MAX ( 'Table'[idticket] )
VAR currentidclient =
MAX ( 'Table'[idclient] )
RETURN
MINX (
FILTER (
ALL ( 'Table' ),
'Table'[idticket] = currentidticket
&& 'Table'[idclient] = currentidclient
),
'Table'[Date_time]
)
= MAX ( 'Table'[Date_time] ), 0,
COUNTROWS ( 'Table' ) > 1, 1 / COUNTROWS ( 'Table' ),
1
)
)
)