Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
I have a table with OpenDate and ClosedDate coloumn in one table. How can I create a common slicer that works with both the data.
Date opened | Closed | Company name | Affected contact | Task |
4/19/2017 | 5/1/2017 | A | B | Incident |
4/28/2017 | 5/1/2017 | A | B | Incident |
2/10/2017 | 5/1/2017 | A | B | Incident |
5/1/2017 | 5/1/2017 | A | B | Incident |
3/20/2017 | 5/1/2017 | A | B | Incident |
4/19/2017 | 5/1/2017 | A | B | Incident |
3/25/2017 | 5/1/2017 | A | B | Incident |
4/11/2017 | 5/1/2017 | A | B | Incident |
4/27/2017 | 5/1/2017 | A | B | Request |
4/28/2017 | A | B | Request | |
5/1/2017 | A | B | Request | |
4/13/2017 | A | B | Request | |
3/10/2017 | A | B | Request | |
4/4/2017 | A | B | Request | |
4/26/2017 | A | B | Request |
Solved! Go to Solution.
@raginkp,
Assume that your original table is named Sample.
Firstly, create a new table using DAX below.
Table = SUMMARIZE('Sample','Sample'[Date opened])
Secondly, create relationship between the new table and Sample table as follows.
Thirdly, create the following measures in the Sample table.
opentask = COUNTA(Sample[Task])
closetask = CALCULATE(COUNTROWS('Sample'),USERELATIONSHIP(Sample[Closed],'Table'[Date opened]))
At last, create a table visual as follows.
Regards,
Lydia
It worked well. I really appreciate your help. Well Done 🙂
Hey buddy
Could you please explain exactly what you want to see with this double slicer?
I don't think it is possible to do that, but with some more information maybe we can find a way...
Maybe using two slicers (one for each column) you will achieve what you want.
Regards
Hi,
I know that I can use two slicers and it worked whenI tried. I do not want to use double slicers in the report, is there any way. I am trying to find out how many tickets were created on that day and how many tickets were closed.
@raginkp,
Assume that your original table is named Sample.
Firstly, create a new table using DAX below.
Table = SUMMARIZE('Sample','Sample'[Date opened])
Secondly, create relationship between the new table and Sample table as follows.
Thirdly, create the following measures in the Sample table.
opentask = COUNTA(Sample[Task])
closetask = CALCULATE(COUNTROWS('Sample'),USERELATIONSHIP(Sample[Closed],'Table'[Date opened]))
At last, create a table visual as follows.
Regards,
Lydia
How can I get the ticket level details in a table for the period using same logic
Hi @raginkp,
Share your dataset and show the expected result.
I have created the data model as she exlained in the solution and it works well for calculated measures. But, when I create a table, it gives wrong results.
It worked well. I really appreciate your help. Well Done 🙂
Hey,
I'm not sure what you are expecting, so I give it a try
A slicer filters the the related table, due to this fact, all other rows (starting on or before the selection and ending on or after are filtered out).
Until now there is no build in feature, that is able to find the records that start on or after and end on or before the slicer selection.
For this reason I create an unrelated calendar table and create a measure that becomes 1 if the row starts on or before and ends on or after (or any other business logic that has to be applied) otherwise 0. No I'm able to filter this rows using visuals. Be aware that measures can become quite complex.
Hope this give you an idea
User | Count |
---|---|
127 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |