Forum Discussion

iris__bi's avatar
iris__bi
Frequent Visitor
1 year ago
Solved

USERELATIONSHIP() - Visual Filter

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)...
Need Help
  • v-dineshya's avatar
    v-dineshya
    1 year ago

    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

iris__bi's avatar
iris__bi
Frequent Visitor
1 year ago

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's avatar
v-dineshya
Icon for Community Support rankCommunity Support
1 year ago

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's avatar
    iris__bi
    Frequent Visitor
    1 year ago

    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's avatar
      v-dineshya
      Icon for Community Support rankCommunity Support
      1 year ago

      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's avatar
        v-dineshya
        Icon for Community Support rankCommunity Support
        1 year ago

        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.