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[date...
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

v-frfei-msft's avatar
v-frfei-msft
Icon for Community Support rankCommunity 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
    Icon for Community Support rankCommunity 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

    • brunofs123's avatar
      brunofs123
      Frequent Visitor
      7 years ago

      Thank you!

       

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

       

      Thanks..