Forum Discussion
USERELATIONSHIP() - Visual Filter
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
Community SupportHI 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
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
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
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