USERELATIONSHIP() - Visual Filter

[iris__bi]

Hi Folks, please help out.

My date table: 

Calendar = 
VAR start_date = MIN(hypothetical_jira_tickets[Created Date])
VAR end_date = TODAY()
RETURN
ADDCOLUMNS(
    CALENDAR(start_date, end_date),
    "Year-Month", FORMAT([Date], "YYYY-MM")
)

>> Having active relationship with created date and inactive with closed date 

In order to plot the number of created and closed ticket on the same line chart, I applied USERELATIONSHIP to closed date as below

Tickets Closed = 
CALCULATE(
    COUNT(hypothetical_jira_tickets[Ticket ID]),
    USERELATIONSHIP('Calendar'[Date],hypothetical_jira_tickets[Closed Date])
    )

For created date, it's just a simple count of the ticket ID (implicit measure)

Thus, the graph is as below:

However, I do want to look enable users to look up those created and closed tickets, so I created a lookup table below, having those information: 

Everything seems good until I click on certain month on the line chart, as illustrated below, the lookup table only shows the specific month's created record, instead of showing the closed records. 

This is understandable since USERELATIONSHIP is temperary measure triggering one-time relationship between tables. Yet, does anyone has any idea how to make the lookup table work? Thanks will really help me out hugely. 

If there's any area explained unclear, please let me know and I'll be more specific. Thanks in advance.

- G'day

[v-dineshya]

HI @iris__bi ,

Thank you for reaching out to the Microsoft Community Forum.

Are you expecting that, the lookup table need to show both created record and closed records. . If you click on certain month in line chart?

If my response has resolved your query, please mark it as the "Accepted Solution" to assist others. Additionally, a "Kudos" would be appreciated if you found my response helpful.

Thank you

[iris__bi]

The lookup table should show 2 set of data depending on which line the user clicks on. Suppose one clicks on ticket closed in May, it should show ONLY the tickets closing in May, not including created (only if the ticket is both created and closed in May). Will that be possible to achieve?

[v-dineshya]

Hi @iris__bi ,

Please follow below steps.

1. Create Disconnected Table

TicketViewType = DATATABLE("View", STRING, {{"Created"}, {"Closed"}})
Add a slicer with TicketViewType[View].

2. Measures for Line Chart

Tickets Created =
CALCULATE(
COUNT('hypothetical_jira_tickets'[Ticket ID]),
TREATAS(VALUES('Calendar_Disconnected'[Date]), 'hypothetical_jira_tickets'[Created Date])
)

Tickets Closed =
CALCULATE(
COUNT('hypothetical_jira_tickets'[Ticket ID]),
USERELATIONSHIP('hypothetical_jira_tickets'[Closed Date], 'Calendar'[Date]),
TREATAS(VALUES('Calendar_Disconnected'[Date]), 'Calendar'[Date])
)

3. Measure to Filter Lookup Table

ShowInLookup =
VAR SelectedYM = SELECTEDVALUE('Calendar_Disconnected'[Year-Month])
VAR SelectedView = SELECTEDVALUE('TicketViewType'[View])
RETURN
IF (
SelectedView = "Created" &&
FORMAT('hypothetical_jira_tickets'[Created Date], "YYYY-MM") = SelectedYM,
1,
IF (
SelectedView = "Closed" &&
FORMAT('hypothetical_jira_tickets'[Closed Date], "YYYY-MM") = SelectedYM,
1,
0
)
)

Add this measure to your lookup table's visual-level filter, set to ShowInLookup = 1.

If my response has resolved your query, please mark it as the "Accepted Solution" to assist others. Additionally, a "Kudos" would be appreciated if you found my response helpful.

Thank you

[iris__bi]

Hi @v-dineshya 

Don't think this works on my case. Is it working on your end (file is in the below link)?

[v-dineshya · Accepted solution]

Hi @iris__bi ,

Please refer attached PBIX file.

If my response has resolved your query, please mark it as the "Accepted Solution" to assist others. Additionally, a "Kudos" would be appreciated if you found my response helpful.

Thank you

[v-dineshya]

Hi @iris__bi ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet.do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Thank you.

[v-dineshya]

Hi @iris__bi ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet.do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Thank you.

[johnt75]

That file is working for me. If you select a month in the line chart the table only shows rows where either the created date or the closed date is in that month.

[iris__bi]

Right, yet the desired result is to click on specific month's created date, showing ticket's created date falling under that month, and vice versa. Not really an OR logic, if that makes sense

[johnt75]

One approach would be to make both relationships inactive and use USERELATIONSHIP in both measures. That would prevent the line chart from filtering to just those rows with a created date in the selected month.

You could then create a measure like

Row is visible =
VAR ChosenDates =
    VALUES ( 'Calendar'[Date] )
VAR Result =
    IF (
        SELECTEDVALUE ( hypothetical_jira_tickets[Closed Date] )
            IN ChosenDates
                || SELECTEDVALUE ( hypothetical_jira_tickets[Created Date] ) IN ChosenDates,
        1
    )
RETURN
    Result

and apply that as a filter to the table visual, set to show only when the value is 1.

[iris__bi]

Hi John, 

Thanks for providing solution, but I don't think that works. I've experimented our solution on my file (link below) but it doesn't seems to work properly. Do you mind taking a look? Much appreciated.

https://drive.google.com/file/d/1ZkxeqKJF_Q9Aqiop9ct2WUFOc9-jOkT3/view?usp=sharing

Iris