Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ashaikh
Helper III
Helper III

Count of resolved ticket for this month where ticket might have been created in next month

Hello,

 

Please help.

 

I have 2 ticket table, say Incident and Calls now these are independent tables and there is no relationship between. And I want to create Power BI report based on these two tables.

Since there is no relationship what I do is create a new table which has data from both table using union. I am looking to create monthly report so I use Start Time to filter record in Report Filter.

 

Now I want to see count of ticket Resolved in a particular month. Since I am using start time tickets created in previous month but resolved in current month are not included.

 

Is there a way I can do it, while also keeping the report filter I have. because there are around 12-15 pages in report and I cant have page level filter.

 

Thanks in advance.

2 ACCEPTED SOLUTIONS

Hi @ashaikh,

 

Please add this measure into a card.

Count Resolved =
CALCULATE (
    COUNT ( Combined[TICKET ID] ),
    FILTER (
        ALL ( Combined ),
        Combined[STATUS] = "Closed"
            && Combined[END DATESTATUS].[MonthNo] = MAX ( Combined[MONTH] )
    )
)

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Hi @ashaikh

 

1. WHen you use the Month column from the combined it will only show the highlighted months, because of the filter context.

 

2. What you need to do is have a separate table of containing Year and Month like - 2017,01, 2017,2 etc... Call this as slicer table.

 

3. Use the Year and Month from this table as a slicers one for each.

 

4. For the meassure count write it as

 Count Resolved =
CALCULATE (
    COUNT ( Combined[TICKET ID] ),
    FILTER (
        ALL ( Combined ),
        Combined[STATUS] = "Closed"
            &&  Combined[END DATESTATUS].[Year] = Max( Slicer[Year] )
            && Combined[END DATESTATUS].[MonthNo] = Max( Slicer[MONTH] )
    )
)

 

If this works for you please accept this as a solutiona and also give KUDOS.

 

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

View solution in original post

5 REPLIES 5
v-yulgu-msft
Employee
Employee

Hi @ashaikh,

 

To better illustrate your scenario, please provide more detailed sample data of both tables, Incident and Calls and screenshot of your desired output. Also, please show us how to determine whether a ticket is resolved.

 

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-yulgu-msft

 

Here are the 3 tables

 

1. IncidentIncident Services.png

 

 

2. Work OrderWork Order.png

 

 

3. CombinedCombined.png

 

 

Now as you can see the report level filter is from month column in Combined table.

 

Here if I select the filter as 8 (August) then the count i will get is for only those record highlighted as yellow. But other than the ones highlighted there are ticket created in previous month were also resolved in 8 - August and I would like to have that count as well.

Tickets are resolved based on the status - Closed

 

Desired output is just a count in a Card or Multi Card visual

 

Thanks

 

Hi @ashaikh

 

1. WHen you use the Month column from the combined it will only show the highlighted months, because of the filter context.

 

2. What you need to do is have a separate table of containing Year and Month like - 2017,01, 2017,2 etc... Call this as slicer table.

 

3. Use the Year and Month from this table as a slicers one for each.

 

4. For the meassure count write it as

 Count Resolved =
CALCULATE (
    COUNT ( Combined[TICKET ID] ),
    FILTER (
        ALL ( Combined ),
        Combined[STATUS] = "Closed"
            &&  Combined[END DATESTATUS].[Year] = Max( Slicer[Year] )
            && Combined[END DATESTATUS].[MonthNo] = Max( Slicer[MONTH] )
    )
)

 

If this works for you please accept this as a solutiona and also give KUDOS.

 

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Thanks for the help.

 

Here is the discrepency I am facing. Under same condition which I spoke earlier if I use following code I get the correct output.

INCIDENT COUNT RESOLVED =
CALCULATE (
DISTINCTCOUNT ( vRCCLCOMBINED[RCCL COMBINED ID] ),
FILTER (
ALL ( vRCCLCOMBINED ),
( vRCCLCOMBINED[STATUS] = "Closed"
|| vRCCLCOMBINED[STATUS] = "Resolved" )
&& vRCCLCOMBINED[END TIME].[MonthNo] = MAX ( vRCCLCOMBINED[MONTH] )
&& vRCCLCOMBINED[END TIME].[Year] = MAX ( vRCCLCOMBINED[END TIME].[Year] )
&& vRCCLCOMBINED[DATA ORIGINATE] = 3
)
)

 

But when I try to use same logic in below code I get blank result. Could you suggest why it is like that.

 

INCIDENT SLA =
SUMMARIZE (
    vRCCLCOMBINED,
    vRCCLCOMBINED[PRIORITY],
    "SLA MET", COUNTX (
        FILTER (
            vRCCLCOMBINED,
            vRCCLCOMBINED[END TIME].[MonthNo] = MAX ( vRCCLCOMBINED[MONTH] )
                && vRCCLCOMBINED[END TIME].[Year] = MAX ( vRCCLCOMBINED[END TIME].[Year] )
                && vRCCLCOMBINED[DATA ORIGINATE] = 3
                && vRCCLCOMBINED[PROGRESS] = "Met"
        ),
        vRCCLCOMBINED[INCIDENT ID SLA]
    ),
    "SLA MISSED", COUNTX (
        FILTER (
            vRCCLCOMBINED,
            vRCCLCOMBINED[END TIME].[MonthNo] = MAX ( vRCCLCOMBINED[MONTH] )
                && vRCCLCOMBINED[END TIME].[Year] = MAX ( vRCCLCOMBINED[END TIME].[Year] )
                && vRCCLCOMBINED[DATA ORIGINATE] = 3
                && vRCCLCOMBINED[PROGRESS] = "Missed"
        ),
        vRCCLCOMBINED[INCIDENT ID SLA]
    ),
    "TICKET COUNT", COUNTX (
        FILTER (
            vRCCLCOMBINED,
            vRCCLCOMBINED[END TIME].[MonthNo] = MAX ( vRCCLCOMBINED[MONTH] )
                && vRCCLCOMBINED[END TIME].[Year] = MAX ( vRCCLCOMBINED[END TIME].[Year] )
                && vRCCLCOMBINED[DATA ORIGINATE] = 3
        ),
        vRCCLCOMBINED[INCIDENT ID SLA]
    ),
    "SLA %", COUNTX (
        FILTER (
            vRCCLCOMBINED,
            vRCCLCOMBINED[END TIME].[MonthNo] = MAX ( vRCCLCOMBINED[MONTH] )
                && vRCCLCOMBINED[END TIME].[Year] = MAX ( vRCCLCOMBINED[END TIME].[Year] )
                && vRCCLCOMBINED[DATA ORIGINATE] = 3
                && vRCCLCOMBINED[PROGRESS] = "Met"
        ),
        vRCCLCOMBINED[INCIDENT ID SLA]
    )
        / COUNTX (
            FILTER (
                vRCCLCOMBINED,
                vRCCLCOMBINED[END TIME].[MonthNo] = MAX ( vRCCLCOMBINED[MONTH] )
                    && vRCCLCOMBINED[END TIME].[Year] = MAX ( vRCCLCOMBINED[END TIME].[Year] )
                    && vRCCLCOMBINED[DATA ORIGINATE] = 3
            ),
            vRCCLCOMBINED[INCIDENT ID SLA]
        )
)

Any suggestion why it could be. Is there any change I need to do.

 

Thanks

Hi @ashaikh,

 

Please add this measure into a card.

Count Resolved =
CALCULATE (
    COUNT ( Combined[TICKET ID] ),
    FILTER (
        ALL ( Combined ),
        Combined[STATUS] = "Closed"
            && Combined[END DATESTATUS].[MonthNo] = MAX ( Combined[MONTH] )
    )
)

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.