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
brunofs123
Frequent Visitor

Dynamic filters - Selected Value - DAX

Hello!

 

I have two tables, CALENDAR TABLE and the Fact Table.

 

In Fact Table I have TWO date columns, let’s call FT[date1] and FT[date2].

 

Both tables are connected. Calendar[Date] à FT[date1]  (picture).

 

 

Table.PNG

My main measure is  simple:

 

volume m3s  = SUM('Outorgas validas  2'[Volume m3s])

 

There is a “catch”. Speaking of 2018, I can only consider the values which are:

 

FT[date1]  >= 01/01/2018   (DD, MM, YYYY)

FT[date2] <=31/12/2017

 

To make it clear, date1 is something like “expiring date”; and date 2 is “publishing date”.

 

What I want:

 

A dynamic FILTER, with the Calendar[Date] in a slicer. When I choose any date or interval, it dynamically apply the filters to calculate, calculating within the conditions. Let’s say I chose “15/01/2007”:

 

FT[date1]  >= 15/01/2007   (DD, MM, YYYY)

FT[date2] <=14/01/2007

 

What I have thought:

 

Result =

 

VAR SELECTED = SELECTEDVALUE(dCalendar[Date])

 

RETURN

CALCULATE([volume m3s];

    FILTER('Outorgas validas  2';

            FT[date1]  >= 15/01/2007   >= SELECTED &&

             FT[date2] <=14/01/2007  <= SELECTED)

  

***

 

But I wasn’t able to get the correct results…

 

Thanks in advance!

 

(english is not my native language)

1 ACCEPTED SOLUTION

Hi @brunofs123,

 

To create a new calculated table and do not create relationship for the new table with other ones.

 

_newCalendar = CALCULATETABLE('dCalendar')

Then we can add the date column in the new table in slicer and create a measure as below.

 

out valid DIN 2 = 
VAR SELECTED = SELECTEDVALUE('_newCalendar'[Date])

RETURN 
 CALCULATE(SUM('FT - Outorgas validas  2'[Volume m3s]),
    FILTER('FT - Outorgas validas  2' ,  
    'FT - Outorgas validas  2'[date venc] >= SELECTED &&
    'FT - Outorgas validas  2'[date public] <= SELECTED)
          )

Capture.PNG

 

Please refer to the pbix.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

4 REPLIES 4
v-frfei-msft
Community Support
Community Support

Hi @brunofs123,

 

Here we can create another calculated table and add the date column of the new table as slicer.

 

_New = Values(Calendar[Date])

And update your measure as below.

 

 

Result =

VAR SELECTED = SELECTEDVALUE(_New[Date])

RETURN

CALCULATE([volume m3s];

FILTER('Outorgas validas 2';

FT[date1] >= 15/01/2007 >= SELECTED &&

FT[date2] <=14/01/2007 <= SELECTED)

If it doesn't meet your requirement, kindly share your sample data and excepted result to me. Please upload your files to One Drive and share the link here.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Thank you for replying. I was only able to see it today, but unfortunately it didn't work.

 

Here is lhe one drive link:

https://onedrive.live.com/?authkey=%21ALpvuHsF7hA32hU&cid=60ABE94B81C766DB&id=60ABE94B81C766DB%21138...

 

I'm afraid i post a wrong calculation in my orginal message:

 

esult =

 

VAR SELECTED = SELECTEDVALUE(dCalendar[Date])

 

RETURN

CALCULATE([volume m3s];

    FILTER('Outorgas validas  2';

            FT[date1]  >= 15/01/2007   >= SELECTED &&

             FT[date2] <=14/01/2007  <= SELECTED)

 

 

please desconsider the red lighted characters

 

 

thanks!

Hi @brunofs123,

 

To create a new calculated table and do not create relationship for the new table with other ones.

 

_newCalendar = CALCULATETABLE('dCalendar')

Then we can add the date column in the new table in slicer and create a measure as below.

 

out valid DIN 2 = 
VAR SELECTED = SELECTEDVALUE('_newCalendar'[Date])

RETURN 
 CALCULATE(SUM('FT - Outorgas validas  2'[Volume m3s]),
    FILTER('FT - Outorgas validas  2' ,  
    'FT - Outorgas validas  2'[date venc] >= SELECTED &&
    'FT - Outorgas validas  2'[date public] <= SELECTED)
          )

Capture.PNG

 

Please refer to the pbix.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Thank you!

 

Mind explaining why you had to create a new and unrelated Calendar?

 

Thanks..

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.