Forum Discussion

marlinajamal's avatar
marlinajamal
New Member
5 years ago
Solved

Pending Tickets/Backlog Count for Distribution Analysis

Hi,

 

Im a noob in Power BI. Just started about 2 months ago on and off.

 

I have data for closed tickets. Each line is unique ticket ID with created date time, closed date time, location code.

I would like to study the distribution for count pending tickets for each new tickets per location.

below is the sample data and intended collumns are the last 2.

 

Appreciate some help

 

TT_NUMBERCREATED_DATECLOSED_DATETIMELOCATION CODEPending Tickets (Overall)Pending Tickets (Per Location)
1-344153546731/6/2020 9:223/7/2020 9:59S00300
1-229338009091/6/2020 10:251/7/2020 17:38S00410
1-344268522551/6/2020 11:371/7/2020 12:43S00120
1-344295039181/6/2020 13:1410/7/2020 20:35S00530
1-344293668141/6/2020 13:427/7/2020 14:51S00141
1-229352287321/6/2020 14:126/7/2020 11:20S00152
1-344392869221/6/2020 20:1810/7/2020 20:10S00561
1-344394154151/6/2020 20:293/7/2020 14:41S00173
1-344418086551/6/2020 23:376/7/2020 20:32S00381
1-349712696911/7/2020 0:011/7/2020 16:19S00592
1-349719549111/7/2020 0:061/7/2020 10:45S002100
INC00021052231/7/2020 0:1112/7/2020 8:33S006110
INC00021052421/7/2020 0:1412/7/2020 8:33S007120
1-230565551271/7/2020 0:151/7/2020 17:24S008130
1-349728603191/7/2020 0:154/7/2020 5:32S006142
1-349725427501/7/2020 0:161/7/2020 20:16S001154
INC00021052741/7/2020 0:2412/7/2020 8:33S006161
INC00021052751/7/2020 0:245/7/2020 1:57S006172
1-349705079311/7/2020 0:241/7/2020 16:24S004181
1-230560489951/7/2020 0:271/7/2020 12:44S009190
Need Help
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    Hi, marlinajamal 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    You may create two calculated columns as below.

    Pending Tickets (Overall) = 
var result=
CALCULATE(
    DISTINCTCOUNT('Table'[TT_NUMBER]),
    FILTER(
        ALL('Table'),
        'Table'[CREATED_DATE]<EARLIER('Table'[CREATED_DATE])
    )
)
return
IF(
    ISBLANK(result),
    0,
    result
)

Pending Tickets (Per Location) = 
var result=
CALCULATE(
    DISTINCTCOUNT('Table'[TT_NUMBER]),
    FILTER(
        ALL('Table'),
        'Table'[CREATED_DATE]<EARLIER('Table'[CREATED_DATE])&&
        'Table'[LOCATION CODE]=EARLIER('Table'[LOCATION CODE])
    )
)
return
IF(
    ISBLANK(result),
    0,
    result
)

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

6 Replies

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    5 years ago

    Hi, marlinajamal 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    You may create two calculated columns as below.

    Pending Tickets (Overall) = 
var result=
CALCULATE(
    DISTINCTCOUNT('Table'[TT_NUMBER]),
    FILTER(
        ALL('Table'),
        'Table'[CREATED_DATE]<EARLIER('Table'[CREATED_DATE])
    )
)
return
IF(
    ISBLANK(result),
    0,
    result
)

Pending Tickets (Per Location) = 
var result=
CALCULATE(
    DISTINCTCOUNT('Table'[TT_NUMBER]),
    FILTER(
        ALL('Table'),
        'Table'[CREATED_DATE]<EARLIER('Table'[CREATED_DATE])&&
        'Table'[LOCATION CODE]=EARLIER('Table'[LOCATION CODE])
    )
)
return
IF(
    ISBLANK(result),
    0,
    result
)

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • marlinajamal's avatar
      marlinajamal
      New Member
      5 years ago

      v-alq-msft ,

       

      Tq so much. You given me a starting point. 

      The CLOSED_DATE TIME should also be one of the parameters, so i add it to the formula and edit the 3rd ticket (1st for S001) CLOSED DATE TIME to be closed before the creation of the 5th tickets for S001 to make sure the formula works.

       

      and it works!

       

      The formula :

       

      Pending Tickets (Overall) = 
var result=
CALCULATE(
    DISTINCTCOUNT('Table'[TT_NUMBER]),
    FILTER(
        ALL('Table'),
        'Table'[CREATED_DATE]<EARLIER('Table'[CREATED_DATE]) && 
        'Table'[CLOSED_DATETIME]>EARLIER('Table'[CREATED_DATE])
    )
)
return
IF(
    ISBLANK(result),
    0,
    result
)


Pending Tickets (Per Location) = 
var result=
CALCULATE(
    DISTINCTCOUNT('Table'[TT_NUMBER]),
    FILTER(
        ALL('Table'),
        'Table'[CREATED_DATE]<EARLIER('Table'[CREATED_DATE])&&
        'Table'[CLOSED_DATETIME]>EARLIER('Table'[CREATED_DATE])&&
        'Table'[LOCATION CODE]=EARLIER('Table'[LOCATION CODE])
    )
)
return
IF(
    ISBLANK(result),
    0,
    result
)

       

       

      The result:

       

      So thank you so much for your help!

      Been stuck for a few weeks tbh

       

      Edited file here

      Pending Tickets Backlog Count for Distribution Analysis_edited.pbix 

       

    • marlinajamal's avatar
      marlinajamal
      New Member
      5 years ago

      amitchandak , the output is calculated collumn that gives value as per the last 2 collumn.

      Pending Tickets (Overall) - when no filter selected

      Pending Tickets Per State - when State Code selected as filter

      • amitchandak's avatar
        amitchandak
        Super User
        5 years ago

        marlinajamal , To me first column (out of 2 last) seems like an index column. It is not going up down based on some logic.  What is the logic for that?

         

        Second column. You can not create a column that can use slicer values.