Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
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.
Solved! Go to Solution.
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
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
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
Here are the 3 tables
1. Incident
2. Work Order
3. Combined
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
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
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
91 | |
86 | |
83 | |
76 | |
49 |
User | Count |
---|---|
145 | |
140 | |
109 | |
68 | |
55 |