cancel
Showing results for
Did you mean:
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).

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…

(english is not my native language)

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Dynamic filters - Selected Value - DAX

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

Regards,

Frank

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

## Re: Dynamic filters - Selected Value - DAX

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

Regards,

Frank

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

## Re: Dynamic filters - Selected Value - DAX

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!

Community Support Team

## Re: Dynamic filters - Selected Value - DAX

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

Regards,

Frank

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

## Re: Dynamic filters - Selected Value - DAX

Thank you!

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

Thanks..

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 112 members 1,436 guests
Recent signins: