cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

how to retrieve the selected values of a date slicer (Direct Query Mode)

Hi Team,

 

Is there any way to retrieve the selected value of a date slicer.That is if i select the slicer from 5/1/2018 to 5/31/2018 its gives the latest date of the column only,How to retrieve the date what ever the selected one.I already tried with the function SELECTEDVALUE but its not work with date slicer.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
thefal Frequent Visitor
Frequent Visitor

Re: how to retrieve the selected values of a date slicer

If you already have a Date table in your source you can just import that one again and give it a new name. I created a date table in SQL Server and imported that. I'm also using Directquery right now for my report 🙂

View solution in original post

6 REPLIES 6
thefal Frequent Visitor
Frequent Visitor

Re: how to retrieve the selected values of a date slicer

Make a new date table that's not connected to other tables, called Cal2.

Make a date slicer using Cal2

 

Created a Measure with the formula 

Selected End = max(Cal2[CalendarDate])

 

Recreate your measures using the Selected End measure.

 

For example:

 

_newMeasure = Calculate(countrows(tickets);
filter(tickets;
(tickets[StopDate]>[Selected Start] && tickets[StopDate]<[Selected End])
))

In this example I made Cal2 for Selcted End, but also a Cal1 table for Selected Start. Works the same way.

Anonymous
Not applicable

Re: how to retrieve the selected values of a date slicer

Hi @thefal,

 

Sorry i missed to one thing,im using direct query so creating a new tale is not possible.

 

Thank you.

Highlighted
thefal Frequent Visitor
Frequent Visitor

Re: how to retrieve the selected values of a date slicer

If you already have a Date table in your source you can just import that one again and give it a new name. I created a date table in SQL Server and imported that. I'm also using Directquery right now for my report 🙂

View solution in original post

Anonymous
Not applicable

Re: how to retrieve the selected values of a date slicer

@thefal,

 

I know this logic but im thinking some thing without a calendar table.

Thank you.

Community Support Team
Community Support Team

Re: how to retrieve the selected values of a date slicer (Direct Query Mode)

Hi @Anonymous,

 

I have made a test with Direct Query mode, you could create the measure below to get the value you seceted in date Slicer.

 

Measure 2 = CONCATENATEX (
    VALUES ( 'SalesLT Product'[SellStartDate]),
    CALCULATE ( SELECTEDVALUE ( 'SalesLT Product'[SellStartDate] ) ),
    " , "
) 

Please note that if we want to use CONCATENATEX function in Direct Query mode, we should enbale it in Options and settings.

Untitled.png

Then you could get the output below.

 

Capture.PNG

 

Hope it can help you!

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
DeekshaUL Frequent Visitor
Frequent Visitor

Re: how to retrieve the selected values of a date slicer

Hi,

I am trying the approach you mentioned for similar scenario. However, running into an issue.

When I create mentioned measure(Selected End = max(Cal2[CalendarDate])), its expected to give the selected value in the slicer. However its giving the max value of non-filtered columns.
e.g. IF Calendar Column holds values from 01-JAN-2010 to 31-DEC-2020 and I selected 15-MAR-2015 in slicer. Then the above measure must give me 15-MAR-2015. However, its returning 31-DEC-2020.

I have tried creating this measure in both Calendar table and Main table:
1. When I create it in Calendar table the slicer selected value is correct. However, I cant pass this value to the main table.
2. If I add the measure in the Main table then it returns the max value of calendar column, not the selected value.

 

Please suggest.

 

Regards,

Deeksha

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 205 members 2,091 guests
Please welcome our newest community members: