Forum Discussion
count rows with some conditions
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 :/.
Super UserI 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
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 ?
yes, that's right. I need to find repeated occurrences for the same idclient and ticket excluding the duplicate values)