Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

How to create a filter with two different crieteria on different tables?

Hello, 

 

I have a table of opened and closed tickets. All I need to do is to create another table of dates and count how many tickets were opened (I mean, not closed) on that specific day. 

eg: 

If a have my tickets in a fact table like this:

 

 

i have to obtain a summarized table considering the status, open and last update columns in order to have how many tickets are open on that day. Using the same table above as an example:

 

it has show the historical data of how many tickets were "Under work" on that day. 

 

I´m very pleased you help me and thanks a lot.

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ，

    I created a sample pbix file(see attachment) for you, please check whether that is what you want.

    1. Create a date dimension table: Tickets by Date

    2. Create a measure as below to get the backlog

    Backlog = 
VAR _curdate =
    SELECTEDVALUE ( 'Tickets by Date'[Date] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Tickets'[Id] ),
        FILTER (
            'Tickets',
            'Tickets'[Open] <= _curdate
                && IF ( 'Tickets'[Status] = "Closed", 'Tickets'[Last Update] >= _curdate, 1 = 1 )
        )
    )

    Best Regards

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    For that I was thinking to use filter formula like this: 

    countrows(CALCULATETABLE(Tickets, Tickets[openDate] <= 'Tickets by Date'[Date], Tickets[UpdatedDate] <= 'Tickets by Date'[Date], Tickets[Status] = "Closed")))

    But it return an multiple table reference error.
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Anonymous ，

      I created a sample pbix file(see attachment) for you, please check whether that is what you want.

      1. Create a date dimension table: Tickets by Date

      2. Create a measure as below to get the backlog

      Backlog = 
VAR _curdate =
    SELECTEDVALUE ( 'Tickets by Date'[Date] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Tickets'[Id] ),
        FILTER (
            'Tickets',
            'Tickets'[Open] <= _curdate
                && IF ( 'Tickets'[Status] = "Closed", 'Tickets'[Last Update] >= _curdate, 1 = 1 )
        )
    )

      Best Regards