Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Dear PBI Community,
Glad to be part of the Power BI users since a few weeks, I learned a lot about Power BI and already generated a lot of reports but today, I'm facing an issue and I can't find a solution.
I'm Incident Manager and I need a graph showing the Open / Closed tickets with the backlog (backlog meaning a picture of how many tickets where opened for a certain period).
I have the following data :
With these data, I'm able to generate this graph :
The "Backlog Tickets" is a measure :
Backlog Tickets = VAR Incoming_Tickets = CALCULATE( COUNT(Fact_Incoming_Closed[Request ID]), FILTER( ALLSELECTED(Fact_Incoming_Closed), Fact_Incoming_Closed[Date]<=MAX('Fact_Incoming_Closed'[Date]) ), FILTER( ALLSELECTED(Fact_Incoming_Closed), Fact_Incoming_Closed[Status]="Incoming" ) ) VAR Closed_Tickets = CALCULATE( COUNT(Fact_Incoming_Closed[Request ID]), FILTER( ALLSELECTED(Fact_Incoming_Closed), Fact_Incoming_Closed[Date]<=MAX('Fact_Incoming_Closed'[Date]) ), FILTER( ALLSELECTED(Fact_Incoming_Closed), Fact_Incoming_Closed[Status]="Closed" ) ) RETURN Incoming_Tickets-Closed_Tickets
Solved! Go to Solution.
HI @yanx1990 ,
You can consider to add all function to ignore all filters to get unfiltered records, then you can use condition filters on these records to get specific range backlog:
Backlog Tickets = VAR currDate = MAX ( 'Fact_Incoming_Closed'[Date] ) VAR Incoming_Tickets = CALCULATE ( COUNT ( Fact_Incoming_Closed[Request ID] ), FILTER ( ALL ( Fact_Incoming_Closed ), Fact_Incoming_Closed[Date] <= currDate && Fact_Incoming_Closed[Status] = "Incoming" ) ) VAR Closed_Tickets = CALCULATE ( COUNT ( Fact_Incoming_Closed[Request ID] ), FILTER ( ALL ( Fact_Incoming_Closed ), Fact_Incoming_Closed[Date] <= currDate && Fact_Incoming_Closed[Status] = "Closed" ) ) RETURN Incoming_Tickets - Closed_Tickets
Regards,
Xiaoxin Sheng
Hi @yanx1990 ,
In my opinion, I think you need to use calendar date(not has relationship to current table) as axis and filter conditions to calculate.
Backlog Tickets = VAR cDate = MAX ( Calendar[Date] ) VAR Incoming_Tickets = CALCULATE ( COUNT ( Fact_Incoming_Closed[Request ID] ), FILTER ( ALLSELECTED ( Fact_Incoming_Closed ), Fact_Incoming_Closed[Date] <= cDate && Fact_Incoming_Closed[Status] = "Incoming" ) ) VAR Closed_Tickets = CALCULATE ( COUNT ( Fact_Incoming_Closed[Request ID] ), FILTER ( ALLSELECTED ( Fact_Incoming_Closed ), Fact_Incoming_Closed[Date] <= cDate && Fact_Incoming_Closed[Status] = "Closed" ) ) RETURN Incoming_Tickets - Closed_Tickets
When you use same table fields as filter, calculation result should been filtered by auto-exist filter if you not add all/allexcept to ignore specific filters.
Regards,
Xiaoxin Sheng
Hi @v-shex-msft ,
Many thanks for your answer :). Unfortunately, this solution doesn't work. PBI returns an error saying :
However, I didn't think about using another table without relation. Maybe I will be able to make it working on another way.
I'll try today and let you know 😉
Thanks a lot !
Yannick
Hi @yanx1990 ,
Can you please share a pbix file with some sample file to test?
Regards,
Xiaoxin Sheng
Hello,
@v-shex-msft Many thanks for your answer. Here's the PBIX and CSV sample data file.
Kind regards,
Yannick
HI @yanx1990 ,
You can consider to add all function to ignore all filters to get unfiltered records, then you can use condition filters on these records to get specific range backlog:
Backlog Tickets = VAR currDate = MAX ( 'Fact_Incoming_Closed'[Date] ) VAR Incoming_Tickets = CALCULATE ( COUNT ( Fact_Incoming_Closed[Request ID] ), FILTER ( ALL ( Fact_Incoming_Closed ), Fact_Incoming_Closed[Date] <= currDate && Fact_Incoming_Closed[Status] = "Incoming" ) ) VAR Closed_Tickets = CALCULATE ( COUNT ( Fact_Incoming_Closed[Request ID] ), FILTER ( ALL ( Fact_Incoming_Closed ), Fact_Incoming_Closed[Date] <= currDate && Fact_Incoming_Closed[Status] = "Closed" ) ) RETURN Incoming_Tickets - Closed_Tickets
Regards,
Xiaoxin Sheng
Hi Xiaoxin Sheng,
@v-shex-msft wrote:HI @yanx1990 ,
You can consider to add all function to ignore all filters to get unfiltered records, then you can use condition filters on these records to get specific range backlog:
Backlog Tickets = VAR currDate = MAX ( 'Fact_Incoming_Closed'[Date] ) VAR Incoming_Tickets = CALCULATE ( COUNT ( Fact_Incoming_Closed[Request ID] ), FILTER ( ALL ( Fact_Incoming_Closed ), Fact_Incoming_Closed[Date] <= currDate && Fact_Incoming_Closed[Status] = "Incoming" ) ) VAR Closed_Tickets = CALCULATE ( COUNT ( Fact_Incoming_Closed[Request ID] ), FILTER ( ALL ( Fact_Incoming_Closed ), Fact_Incoming_Closed[Date] <= currDate && Fact_Incoming_Closed[Status] = "Closed" ) ) RETURN Incoming_Tickets - Closed_TicketsRegards,
Xiaoxin Sheng
@v-shex-msft wrote:HI @yanx1990 ,
You can consider to add all function to ignore all filters to get unfiltered records, then you can use condition filters on these records to get specific range backlog:
Backlog Tickets = VAR currDate = MAX ( 'Fact_Incoming_Closed'[Date] ) VAR Incoming_Tickets = CALCULATE ( COUNT ( Fact_Incoming_Closed[Request ID] ), FILTER ( ALL ( Fact_Incoming_Closed ), Fact_Incoming_Closed[Date] <= currDate && Fact_Incoming_Closed[Status] = "Incoming" ) ) VAR Closed_Tickets = CALCULATE ( COUNT ( Fact_Incoming_Closed[Request ID] ), FILTER ( ALL ( Fact_Incoming_Closed ), Fact_Incoming_Closed[Date] <= currDate && Fact_Incoming_Closed[Status] = "Closed" ) ) RETURN Incoming_Tickets - Closed_TicketsRegards,
Xiaoxin Sheng
@v-shex-msft wrote:HI @yanx1990 ,
You can consider to add all function to ignore all filters to get unfiltered records, then you can use condition filters on these records to get specific range backlog:
Backlog Tickets = VAR currDate = MAX ( 'Fact_Incoming_Closed'[Date] ) VAR Incoming_Tickets = CALCULATE ( COUNT ( Fact_Incoming_Closed[Request ID] ), FILTER ( ALL ( Fact_Incoming_Closed ), Fact_Incoming_Closed[Date] <= currDate && Fact_Incoming_Closed[Status] = "Incoming" ) ) VAR Closed_Tickets = CALCULATE ( COUNT ( Fact_Incoming_Closed[Request ID] ), FILTER ( ALL ( Fact_Incoming_Closed ), Fact_Incoming_Closed[Date] <= currDate && Fact_Incoming_Closed[Status] = "Closed" ) ) RETURN Incoming_Tickets - Closed_TicketsRegards,
Xiaoxin Sheng
Hi @v-shex-msft ,
It works like a charm !! Tested on my production database (the CSV was just a sample) and it's just what I expected to do.
Again many many thanks and have a wonderful day
Yannick
User | Count |
---|---|
98 | |
91 | |
84 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
66 |