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

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