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
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

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

8 REPLIES 8
v-piga-msft
Resident Rockstar
Resident Rockstar

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.

Hi, could you please

share the DAX of Measure and not measure 2.

thefal
Frequent Visitor

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

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

Hi Deeksha,

 

Were you able to find a solution for this problem? I am facing somewhat similar issue.

 

Cheers,

Nidhi

Anonymous
Not applicable

Hi @thefal,

 

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

 

Thank you.

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 🙂

Anonymous
Not applicable

@thefal,

 

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

Thank you.

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.