cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Pingu Frequent Visitor
Frequent Visitor

Filter( dates based)

hi,

 

I have a table with tickets:

Ticket_ID  /    Day_Open   / Day_Closed / ...

and a Calendar table with dates

 

I'd like a measure to cound the amount of open tickets any given day so that I can chart it.

 

So  I tried:

OpenTickets=  COUNTROWS(FILTER(Table; Table[Day_Open].[Date]< MIN(Calendar[Date]) && (Table[Day_Closed].[Date]> MIN(Calendar[Date]) || Table[Day_Closed].[Date]= BLANK())))

 

but I doesn't work. I had to put MIN (Date)  and I suppose this is the problem, but I don't know how to filter using the given  checked day on a measure.

 

any tip?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Filter( dates based)

Hi Pingu,

 

You said "I'd like a measure to cound the amount of open tickets any given day so that I can chart it.", if "any given day" means every day in the calendar table. You can create a relationship between Ticket table and Calendar table and modify your measure like below:

OpenTickets =
COUNTROWS (
    FILTER (
        Table;
        Table[Day_Open].[Date] < RELATED ( Calendar[Date] )
            && (
                Table[Day_Closed].[Date] > RELATED ( Calendar[Date] )
                    || Table[Day_Closed].[Date] = BLANK ()
            )
    )
)

If it means date user has selected, after creating relationship, you can create a slicer chart based on Calendar column and modify measure like this:

OpenTickets =
COUNTROWS (
    FILTER (
        Table;
        Table[Day_Open].[Date] < SELECTEDVALUE ( Calendar[Date] )
            && (
                Table[Day_Closed].[Date] > SELECTEDVALUE ( Calendar[Date] )
                    || Table[Day_Closed].[Date] = BLANK ()
            )
    )
)

Regards,

Jimmy Tao

1 REPLY 1
Community Support Team
Community Support Team

Re: Filter( dates based)

Hi Pingu,

 

You said "I'd like a measure to cound the amount of open tickets any given day so that I can chart it.", if "any given day" means every day in the calendar table. You can create a relationship between Ticket table and Calendar table and modify your measure like below:

OpenTickets =
COUNTROWS (
    FILTER (
        Table;
        Table[Day_Open].[Date] < RELATED ( Calendar[Date] )
            && (
                Table[Day_Closed].[Date] > RELATED ( Calendar[Date] )
                    || Table[Day_Closed].[Date] = BLANK ()
            )
    )
)

If it means date user has selected, after creating relationship, you can create a slicer chart based on Calendar column and modify measure like this:

OpenTickets =
COUNTROWS (
    FILTER (
        Table;
        Table[Day_Open].[Date] < SELECTEDVALUE ( Calendar[Date] )
            && (
                Table[Day_Closed].[Date] > SELECTEDVALUE ( Calendar[Date] )
                    || Table[Day_Closed].[Date] = BLANK ()
            )
    )
)

Regards,

Jimmy Tao