Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
98 | |
80 | |
67 | |
63 |
User | Count |
---|---|
145 | |
111 | |
104 | |
84 | |
64 |