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.
Hi Power BI Community,
I have a Ticket table and a measure that calculates for a selected time range: how many tickets were opened befor that time range but was closed in that time range (or later).
id | created | solved |
1 | 15.10.2020 15:06:23 | 20.10.2020 09:30:09 |
2 | 14.10.2020 13:55:27 | 16.10.2020 10:14:55 |
3 | 16.10.2020 08:57:33 | 02.11.2020 07:43:09 |
4 | 21.10.2020 11:03:44 |
The measure:
Old Tickets =
VAR minDate = CALCULATE(MIN('Date'[Date]))
VAR oldTickets = CALCULATE(COUNTROWS(Tickets),Tickets[created]<minDate,OR(Tickets[solved]>=minDate,ISBLANK(Tickets[solved])))
RETURN
oldTickets
With drillthrough (on KW43) I would like to show a details table which includes the tickets id 1 and 3
On page "Show old tickets" is table, showing the ticket data. I added a calculated column which evaluates to true/false. Goal was to filter the table to show only rows where the "ShowinDetailsTable" formula is true. Doesn't work because in the calculated column formula the filter is not active.
ShowinDetailsTable =
//calculate for each row if it is to be shown for current filter settings: ticket was created before filtered, but was solved later
//like in measure "Old Tickets" select first date for current filter (since we are in calculated column this is not the first date of filter, but first date at all in Date table)
VAR minDate = CALCULATE(MIN('Date'[Date]))
VAR show = IF(
AND(
Tickets[created]<minDate,
OR(
Tickets[solved]>=minDate,
ISBLANK(Tickets[solved])
)
),
TRUE(),
FALSE())
RETURN
show
I think I would some kind of "measure table" and show results directly in table visual
Example PBIX File: https://www.dropbox.com/sh/iw4snh0goeoeoy5/AABlG9V2yGacQWvKbgWA62XXa?dl=0
Thanks for any comments or ideas to realize this in Power BI
Solved! Go to Solution.
My bad. You can use a measure like this to use in the Filter Panel for the table visual where it "is" 1.
Note you may have to adapt for when a ticket is closed on the last day of the period as it has a time component greater than the 12 AM default.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
My bad. You can use a measure like this to use in the Filter Panel for the table visual where it "is" 1.
Note you may have to adapt for when a ticket is closed on the last day of the period as it has a time component greater than the 12 AM default.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Please try an expression like this to show the number of open tickets in any give date context (with no relationship between your tables). If you do need a relationship (for other analyses), you can simply add an ALL into the CALCULATE. You should be able to adapt this measure pattern, if needed.
Open Tickets =
VAR vMinDate =
MIN ( 'Date'[Date] )
VAR vMaxDate =
MAX ( 'Date'[Date] )
RETURN
CALCULATE (
COUNTROWS ( Tickets ),
Tickets[created] <= vMaxDate,
Tickets[solved] >= vMinDate
)
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@mahoneypat thanks! I think I already got the measure "old Tickets":
Old Tickets =
VAR minDate = CALCULATE(MIN('Date'[Date]))
VAR oldTickets = CALCULATE(COUNTROWS(Tickets),Tickets[created]<minDate,OR(Tickets[solved]>=minDate,ISBLANK(Tickets[solved])))
RETURN
oldTickets
And I can visualize the number of "open/old tickets" in bar chart.
Now I would like to select one bar (= select a week) via drillthrough and show all tickets that are "old/open" in this week, not just the number of tickets.
If selected Week 43 I would like to the following table showing the tickets with id "1" and "3"
@reikermann , are you not able calculate open tickets , if yes refer :https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
@amitchandak , thanks for the reference. I'm not sure how your reference could help in this case. I think your case it would be show a table with all employees employed in selected "Month Year" on your "Dashboard"
Since you have an relationship between "date" and "employee start" in a table you could only show employees started in selected "Month Year" but one would like to show all employees started anytime before selected "Month Year"
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 |
---|---|
92 | |
89 | |
83 | |
78 | |
49 |
User | Count |
---|---|
146 | |
137 | |
109 | |
68 | |
55 |