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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

@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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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