How to add a common Slicer for two different date fields in a table

[raginkp]

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

[Anonymous · Accepted 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

[Gui_Costa]

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

[raginkp]

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.

[Anonymous · Accepted 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

[raginkp]

How can I get the ticket level details in a table for the period using same logic

[Ashish_Mathur]

Hi @raginkp,

Share your dataset and show the expected result.

[raginkp]

@Anonymous @Ashish_Mathur

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. 

[raginkp · Accepted solution]

It worked well. I really appreciate your help. Well Done 🙂

[TomMartens]

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