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