performance between 2 dates in fact table and extract a propertie

[Cobra77]

Hi ,
We have a fairly large fact table, on which we must count the number of tickets still open to date.
This requires to browse each time the whole table according to the past date.
It already takes a while if we do it for a chart with all months for a year.
Where it explodes completely is when we want to know for example which are the 5 tickets open in December.
(Whether the ticket is put in a dimension or in the fact table, same)

Nb Backlog = 
VAR DateMaxCal = MAX ( '01 - Calendrier'[Date] )
VAR DateMaxTF = LASTDATE('Tickets'[SDR_CreationDateUT])

VAR DateMax = If ( DateMaxCal > DateMaxTF , DateMaxTF , DateMaxCal )

VAR Fi =
    FILTER (
        'Tickets',
        'Tickets'[ACT_CreationDateUT] <= DateMax
            && (
                'Tickets'[ACT_EndDateUT] > DateMax
                    || ISBLANK ( 'Tickets'[ACT_EndDateUT] )
            )
            && ( 'Tickets'[ACT_ACTION_TYPE_ID] = 2
            || 'Tickets'[ACT_ACTION_TYPE_ID] = 20
            || 'Tickets'[ACT_ACTION_TYPE_ID] = 32
            || 'Tickets'[ACT_ACTION_TYPE_ID] = 34
            || 'Tickets'[ACT_ACTION_TYPE_ID] = 69 )
                       
    )
    
    
RETURN
    CALCULATE ( SUMX ( VALUES ( 'Tickets'[ACT_IdOrigine] ), 1 ), Fi,  '24 - Action Statut Fin'[Statut] <> "Incident lié" ,  '24 - Action Statut Fin'[Statut] <> "Commande & Réception")<p> </p><p>An idea ? modelisation or dax measure ?</p><p>Thanks for your help.</p><p> </p>

[speedramps]

Consider the following ….

Use MAX(Ticket[Date]) rather  LASTDATE(Ticket[Date])  

Use 'Tickets'[ACT_ACTION_TYPE_ID] in { 2, 20, 32, 34, 69}  

Use COUNTROWS('Tickets') instead of SUMX ( VALUES ( 'Tickets'[ACT_IdOrigine] ), 1)

Use Power Query to set an integer indicator for "Incident lié” and "Commande & Réception", rather than do DAX string comparisons on a large dataset.

[Cobra77]

the calculation even with the modifications recommended there when it is for a date is correct

example 2583 tickets still open on this date, it is when we want to know the number/reference of the 2583 tickets concerned that this explodes the duration.

thanks

[amitchandak]

@Cobra77 , is performance is an issue or calculation?

For performance try like

VAR Fi =
    FILTER (
        'Tickets',
        'Tickets'[ACT_CreationDateUT] <= DateMax
            && (
                'Tickets'[ACT_EndDateUT] > DateMax
                    || ISBLANK ( 'Tickets'[ACT_EndDateUT] )
            )
            && ( 'Tickets'[ACT_ACTION_TYPE_ID] in{ 2, 20, 32, 34, 69}  )
                       
    )

for formula, you can refer 

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...