Forum Discussion

brunofs123's avatar
brunofs123
Frequent Visitor
7 years ago
Solved

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…

 

Thanks in advance!

 

(english is not my native language)

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

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

     

    Please refer to the pbix.

     

    Regards,

    Frank

4 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    7 years ago

    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

    • brunofs123's avatar
      brunofs123
      Frequent Visitor
      7 years ago

      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%211382&parId=60ABE94B81C766DB%21105&o=OneUp

       

      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!

      • v-frfei-msft's avatar
        v-frfei-msft
        Community Support
        7 years ago

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

         

        Please refer to the pbix.

         

        Regards,

        Frank