Create a filter based on a measure

Hi Anonymous ,

Yes, it's possible. You can download my solution proposal from here.

Here are the steps to obtain that:

1) Create a calculated Date table (Modeling -> New Table). You can create it with the following DAX formula:

Date = CALENDARAUTO()

2) Create a non-calculated Status Table (Home -> Enter Data).

3) Create a calculated measure to obtain the current status. The status will depend on:

- the date selected (the status will be different based on the data)

- the status selected ('opened' tickets will be shown only if there is an 'opened' filter in Status or no filter in Status)

Here is the DAX formula:

Current Status = 
VAR selectedDate = MAX('Date'[Date])

VAR openDate = SELECTEDVALUE('Tickets'[Open Date])
VAR startDate = SELECTEDVALUE('Tickets'[Start Date])
VAR closeDate = SELECTEDVALUE('Tickets'[Close Date])

VAR ticketStatus = IF(selectedDate<openDate, BLANK(), IF(selectedDate<startDate,"Opened",IF(selectedDate<closeDate,"Started","Closed")))

VAR isStatusFiltered = ISFILTERED('Status'[Status])

RETURN IF(isStatusFiltered, IF(ticketStatus=SELECTEDVALUE('Status'[Status]),ticketStatus,BLANK()), ticketStatus)

Below is a screenshot of what it looks like:

Hope this helps you. 

LC

Interested in Power BI and DAX templates? Check out my blog at www.finance-bi.com