Show data in Table with non trivial logic

[reikermann]

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

Now I like to add a Drillthrough that show these old tickets in a table. My problem is: normal filter with relationships seems to be not possible. Next try was a calculated column that indicates with true/false, if this row should be showed for current filter selection. Problem here is calculated column has no access/affect to current filter and a measure cannot be evaluated in context of a row.

In following picture you can see in left table, the abstract ticket data: id, created (date), solved (date), CreatedWeek and solvedWeek is just the Week of the corresponding date. 
On the right you can see the "old tickets" measure, for KW 43 (= Week  43 of year) it is "2" because tickets id 1 and 3 are created before KW43 but were solved in KW 43 or later. Ticket  id"2" is not counted as "old" because it was created in KW42 and solved in KW42.   
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

[mahoneypat · Accepted solution]

My bad.  You can use a measure like this to use in the Filter Panel for the table visual where it "is" 1. 

IsOpen = var vMinDate = MIN('Date'[Date])

var vMaxDate = MAX('Date'[Date])

var thiscreated = MIN(Tickets[created])

var thissolved = MIN(Tickets[solved])

return if(AND(thiscreated<=vMaxDate, thissolved>=vMinDate), 1, 0)

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

[mahoneypat]

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

[reikermann]

@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"

[amitchandak]

@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...

[reikermann]

@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"