locked
Selecting the value of a cell in a table based on another cell in the same table RRS feed

  • Question

  • I'm using Excel to query data in a SSAS Tabular model and have converted the PivotTable to formulas. Is there a formula/function that I can use to return the value in a table based on a filter/selection from the report? 

    My scenario is:

    - Date dimension at the week level (custom weeks for each year, meaning I can have more or less than 52 rows for a given year)
    - I want to find the date key for the Week Name selected (i.e. 2014WK1 Jan 1 - Jan 6 has a corresponding DateKey=1)
    - Based on that date key, add or subtract 1 to get the date key for the prior week and the following week
    - Using those date keys, look up the corresponding Week Name and show it in the report


    Victor Rocca

    Wednesday, September 3, 2014 12:40 AM

Answers

  • I found a solution that seems to meet my requirements. What I ended up doing was use a combination of the CUBESET() and CUBERANKEDMEMBER() functions in Excel. The CUBESET() returns the Weeks-n, and with the CUBERANKEDMEMBER() I can iterate through the result set by stepping through the ranks.

    Victor Rocca

    • Marked as answer by Michael Amadi Tuesday, September 16, 2014 1:38 PM
    Monday, September 8, 2014 11:38 PM

All replies

  • If you have rights to modify tabular model then, you can create a measure in your Tabular model which returns previous week and then use this measure in Pivot Table.

    Thanks,

    Sagar K 

    (Blog: http://datamazik.blogspot.in/)


    • Proposed as answer by SagarK.CF Monday, September 8, 2014 3:47 AM
    Thursday, September 4, 2014 7:32 AM
  • I found a solution that seems to meet my requirements. What I ended up doing was use a combination of the CUBESET() and CUBERANKEDMEMBER() functions in Excel. The CUBESET() returns the Weeks-n, and with the CUBERANKEDMEMBER() I can iterate through the result set by stepping through the ranks.

    Victor Rocca

    • Marked as answer by Michael Amadi Tuesday, September 16, 2014 1:38 PM
    Monday, September 8, 2014 11:38 PM
  • If you want to do this in DAX you can use something along the lines of the following (I am just using generic names since you've not shared sample data):

    LastWeekMeasure :=
    CALCULATE (
        [Some Measure],
        FILTER (
            ALL ( Calendar ),
            Calendar[WeekKey]
                = MAX ( Calendar[WeekKey] ) - 1
        )
    )

    To make the subsequent week version is a trivial change of the - to a +. As is, this will display the week name selected in the slicer as the row filter, but the values returned will be for the prior or next week. If you do need the row filter to show the prior week name and subsequent week name respectively, that can be done - let me know.

    Doing this in DAX allows more portability and flexibility than using Excel's cube formulas, but ultimately you're the only one who can decide what fits your use case best.


    Tuesday, September 9, 2014 4:51 AM