Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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
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
Your solution works like a charm!
Nice idea to use a non calculated status table using it for the filter and matching it with the measure.
Many thanks
HI @Anonymous ,
glad this helped you!
Do not hesitate if you have further questions,
LC
Hi @lc_finance ,
I have a problem with your solution.
If, for example, I'd like to count the number of ticket with a simple card, the status filter will not work on it.
To replicate it, just add a card, select the Tickets[ID] and use the count function on it.
It will always show 2.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
20 | |
19 | |
15 | |
15 |
User | Count |
---|---|
46 | |
28 | |
25 | |
18 | |
17 |