none
Use Slicer value in DAX

    Question

  • Hi All,

    I want to link my Slicers values with DAX expression. What I want is If  I select any month & Year from Month & Year Slicer

    e.g (March Month & 2013 Year) then it should show table as:


                                 March - May2011 | March-May2012 | March-May2013

    ----------------------------------------------------------------------------------------------------

    Sum Of Games             200            |          300           |         400

    Sum Of Assets             150            |          400            |        600

    Basically it should calculate values from selected month to (Selected month +3) & selected year to (Selected Year - 3).

    So is it possible using Slicer selection dynamically?

    I have done this in static way. For reference you can see this link

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/73735550-2a8c-4270-b09c-70c98aec5eb8/grouping-based-on-month-range?forum=sqlkjpowerpivotforexcel

    but I want it based on user selection dynamically.

    Thanks in Advance!



    Wednesday, February 05, 2014 3:01 PM

Answers

  • ok, try these three calculated measures:

    3M_Name1:=IF(HASONEVALUE('Date'[Month Name]), VALUES('Date'[Month Name]))

    3M_Name3:=CALCULATE([3M_Name1], DATEADD('Date'[Date], 2, MONTH))

    3M_Names:=IF(NOT(ISBLANK([3M_Name1])) && NOT(ISBLANK([SumSA])), [3M_Name1] & " - " & [3M_Name3])

    you can use [3M_Names] also in a CUBEVALUE-functions in combination with Slicer_MonthName and Slicer_CalendarYear

    of course you can get the year in a similar way and may append it to your measure

    hth,
    gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    Friday, February 07, 2014 3:34 PM

All replies

  • Hi,

    those calculation should work:

    3M_CY:=CALCULATE([SumSA], DATESINPERIOD('Date'[Date], MIN('Date'[Date]), 3, MONTH))

    3M_PY:=CALCULATE([3M_CY], SAMEPERIODLASTYEAR('Date'[Date]))

    3M_PPY:=CALCULATE([3M_PY], SAMEPERIODLASTYEAR('Date'[Date]))

    though, it will not be possible to set the name of them dynamically as in your example

    hth,
    gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    Wednesday, February 05, 2014 7:29 PM
  • Hi Gerhard,

    Actually I didn't get how you have written the Dax.

    N where the slicer's value is coming into picture inside the Dax?

    again 3M_CY, 3M_PY, 3M_PY are these 3 calculated columns inside data model?

    Could you you please elaborate it?


    Thursday, February 06, 2014 8:57 AM
  • Hi,

    no, this are calculated measures

    the slicer-value comes into play by referring to 'Date'[Date] which is then filtered by the slicer

    hth,
    gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    Thursday, February 06, 2014 12:24 PM
  • Hi Gerhard,

    Following DAx is calculating sum of 1 month only not 3 months.

    3M_CY:=CALCULATE([SumSA], DATESINPERIOD('Date'[Date], MIN('Date'[Date]), 3, MONTH))

    I guess DATESINPERIOD is not working correctly because of Slicers.

    The reason is when I select any Month in the slicer, it will get only those data in which that specific month is present so how can it will get data of post 3 months?



    Thursday, February 06, 2014 1:38 PM
  • i guess you have not set your date-table and date-column accordingly

    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    Thursday, February 06, 2014 1:59 PM
  • Could you please tell me how should I set those date-table & date-column accordingly?
    Friday, February 07, 2014 5:03 AM
  • first hit on google - pretty much explains everything:

    http://office.microsoft.com/en-001/excel-help/understand-and-create-date-tables-in-power-pivot-in-excel-2013-HA104139621.aspx#_Toc371340684

    Point 24 is how to mark a date-table

    hth,
    gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    Friday, February 07, 2014 9:00 AM
  • OMG. It worked finally. :)

    Thanks Gerhard!!!

    Setting Date Table property was the clue.Thanks a lot!!

    But as you said it will not be possible to set the name of them dynamically.

    Is there any workaround on it?

    I tried one way where in I can get selected month & Year (March2012) of slicer by using following Formulae

    =CUBERANKEDMEMBER("ThisWorkbookDataModel",Slicer_MonthName,1) & CUBERANKEDMEMBER("ThisWorkbookDataModel",Slicer_CalendarYear,1)

    But unable to get the month range as March-May2010.

    Please see if you have any solutions on the same.

    Friday, February 07, 2014 1:28 PM
  • ok, try these three calculated measures:

    3M_Name1:=IF(HASONEVALUE('Date'[Month Name]), VALUES('Date'[Month Name]))

    3M_Name3:=CALCULATE([3M_Name1], DATEADD('Date'[Date], 2, MONTH))

    3M_Names:=IF(NOT(ISBLANK([3M_Name1])) && NOT(ISBLANK([SumSA])), [3M_Name1] & " - " & [3M_Name3])

    you can use [3M_Names] also in a CUBEVALUE-functions in combination with Slicer_MonthName and Slicer_CalendarYear

    of course you can get the year in a similar way and may append it to your measure

    hth,
    gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    Friday, February 07, 2014 3:34 PM
  • Yes Perfect!!!

          Mar-Jun11    Mar-Jun12 |  Mar10-Feb11   Mar11-Feb12  Mar12-Feb13

    A        12                 45         |     65                 52                        68

    B        48                 75         |      36                 42                        78

    C        42                 23         |      71                 26                        21

    D        78                 42         |      62                 35                        96

    E        77                 23         |     98                 72                         24

    But Gerhard, as you see above I have around 25 cells to display such data so if I will start to create measures like this it will be around 25 + (15 for showing proper column headers) = 40 measures I will have to include in my table.

    Is it good to have these many number of measures in one model?

    again it will create problem while arranging into tabular form(I mean measures only go into Pivot Field Values section not into ROWS section).

    your comments on this.

    Monday, February 10, 2014 5:28 AM
  • yes, those are measures and have to be placed in the Values-section of your pivot table along with all your other measures and it will be very hard (if not impossible) for you to use a pivot-table for your final report

    another option would be to use a Formula report instead, you are much more flexible in terms of lay-outing then

    you may also reconsider the Meausre-approach and use native Excel formulas instead - e.g. create only one measure that returns MAX(Date[Date]) as a date-value and calculated all Headings based on that value


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    Monday, February 10, 2014 8:35 AM
  • Okay right..

    I think I will have to reconsider the use of Measure approach.

    anyways Thanks a lot Gerhard! :)

    Tuesday, February 11, 2014 9:08 AM
  • Hi Gerhard,

    Any other solution to this than Measure approach?

    Friday, February 21, 2014 8:26 AM
  • do you mean for the headers or for the values?

    for the headers im afraid there is no other solution - at least i cannot think of one yet

    for the calculations you may use a separate table to control the number of years that you want to shift back and use a dynamic calculation in a DATEADD-function


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    Monday, February 24, 2014 8:41 AM
  • No no.  I mean other approach like use of CUBE function such as

    =CUBEVALUE("ThisWorkBook","[Measures].[sumSA]","[Dim_Date].[CalendarYear].["&E7&"]",Slicer_Month)

    Cell E7 has selected value of Calendar Year Slicer.

    by using above function I am able to get the data at selected Calendar year & for month I have to select it manually.

    But I don't want to select month manually, I want if select any month, then I should I get data from that selected month to 3month's ahead(Quarter)

    Is it possible?

    Thursday, February 27, 2014 1:57 PM