performance between 2 dates in fact table and extract a propertie

Question

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)&nbsp;Nb Backlog = 
VAR DateMaxCal = MAX ( '01 - Calendrier'[Date] )
VAR DateMaxTF = LASTDATE('Tickets'[SDR_CreationDateUT])

VAR DateMax = If ( DateMaxCal &gt; DateMaxTF , DateMaxTF , DateMaxCal )

VAR Fi =
    FILTER (
        'Tickets',
        'Tickets'[ACT_CreationDateUT] &lt;= DateMax
            &amp;&amp; (
                'Tickets'[ACT_EndDateUT] &gt; DateMax
                    || ISBLANK ( 'Tickets'[ACT_EndDateUT] )
            )
            &amp;&amp; ( '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] &lt;&gt; "Incident lié" ,  '24 - Action Statut Fin'[Statut] &lt;&gt; "Commande &amp; Réception")&lt;p&gt; &lt;/p&gt;&lt;p&gt;An idea ? modelisation or dax measure ?&lt;/p&gt;&lt;p&gt;Thanks for your help.&lt;/p&gt;&lt;p&gt; &lt;/p&gt;&nbsp;

amitchandak · Answer

Cobra77 , is performance is an issue or calculation?
&nbsp;
For performance try like
VAR Fi =
    FILTER (
        'Tickets',
        'Tickets'[ACT_CreationDateUT] &lt;= DateMax
            &amp;&amp; (
                'Tickets'[ACT_EndDateUT] &gt; DateMax
                    || ISBLANK ( 'Tickets'[ACT_EndDateUT] )
            )
            &amp;&amp; ( 'Tickets'[ACT_ACTION_TYPE_ID] in{ 2, 20, 32, 34, 69}  )
                       
    )
&nbsp;
for formula, you can refer&nbsp;
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

speedramps · Answer

Consider the following ….Use MAX(Ticket[Date]) rather &nbsp;LASTDATE(Ticket[Date]) &nbsp;Use 'Tickets'[ACT_ACTION_TYPE_ID] in { 2, 20, 32, 34, 69} &nbsp;Use COUNTROWS('Tickets') instead of SUMX ( VALUES ( 'Tickets'[ACT_IdOrigine] ), 1)Use Power Query to set an integer indicator for "Incident lié” and "Commande &amp; Réception", rather than do DAX string comparisons on a large dataset.

cobra77 · Answer

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

Forum Discussion

performance between 2 dates in fact table and extract a propertie

3 Replies