Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
reikermann
Frequent Visitor

Show data in Table with non trivial logic

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

idcreatedsolved
1 15.10.2020 15:06:2320.10.2020 09:30:09
214.10.2020 13:55:2716.10.2020 10:14:55
316.10.2020 08:57:3302.11.2020 07:43:09
421.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.   
image.png

 

With drillthrough (on KW43) I would like to show a details table which includes the tickets id 1 and 3
image.png

 

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
​


image.png

 

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

 

 

1 ACCEPTED SOLUTION
mahoneypat
Microsoft Employee
Microsoft Employee

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)
 
example.png
 

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

 

 

 

 
 

 

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

5 REPLIES 5
mahoneypat
Microsoft Employee
Microsoft Employee

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)
 
example.png
 

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

 

 

 

 
 

 

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


mahoneypat
Microsoft Employee
Microsoft Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


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

image.png

 

 

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"
image.png

amitchandak
Super User
Super User

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.