Forum Discussion
Create a filter based on a measure
I have a dataset with a lot of tickets.
A tickets has an id, an open date, a start date and a close date.
The end user has to possibility to select a single date and this will change the status of the tickets.
For example:
ID, Open Date, Start Date, Close Date
1, 01/01/2019, 10/02/2019, 12/03/2019
2, 01/02/2019, 10/01/2019, 12/01/2019
If the date selected is between Open Date and Start Date a ticket is open, if is between Start Date and Close Date the ticket is started, if is after Close Date the ticket is Closed.
I'd like to have a filter that on the context of the data selected, will give only the open, or the started, or the closed tickets.
I can't do it with a measure because I can't set a measure as a filter and I can't do it with a calculated column because is not dynamic.
Any suggestion if it is possible and how?
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
4 Replies
lc_financeSolution Sage
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
- lc_finance
HI Anonymous ,
glad this helped you!
Do not hesitate if you have further questions,
LC