Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
raginkp
Frequent Visitor

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

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 openedClosedCompany nameAffected contactTask
4/19/20175/1/2017ABIncident
4/28/20175/1/2017ABIncident
2/10/20175/1/2017ABIncident
5/1/20175/1/2017ABIncident
3/20/20175/1/2017ABIncident
4/19/20175/1/2017ABIncident
3/25/20175/1/2017ABIncident
4/11/20175/1/2017ABIncident
4/27/20175/1/2017ABRequest
4/28/2017 ABRequest
5/1/2017 ABRequest
4/13/2017 ABRequest
3/10/2017 ABRequest
4/4/2017 ABRequest
4/26/2017 ABRequest

 

 

2 ACCEPTED SOLUTIONS

@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.
1.JPG2.JPG3.JPG

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.
4.JPG5.JPG



Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

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

View solution in original post

8 REPLIES 8
Gui_Costa
Frequent Visitor

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.
1.JPG2.JPG3.JPG

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.
4.JPG5.JPG



Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@v-yuezhe-msft @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. 

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

TomMartens
Super User
Super User

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.