Forum Discussion

someone's avatar
someone
Frequent Visitor
8 years ago
Solved

Using a Slicer to Always Display Previous 4 Rows without

I have gotten a request to make the report I'm working on always display the previous four quarters (without using multi-select) depending on which selection is made. The data is organized as follows...
Need Help
Tutorial Requests
  • v-juanli-msft's avatar
    v-juanli-msft
    8 years ago

    Hi someone

    1. In query editor, create an index column from 1.

       Then create a blank query, and write code in Advanced editor.

    let
    Source = Table.SelectColumns(Sheet1,{"Quarter","Index"})
in
    Source

     

    2. In Relationship View, create the relationship between two tables.

     

    3. In Report view, create measures, then add [Measure 3] to the Visual Filter and show 1

    Measure = SELECTEDVALUE(Query1[Index])

Measure 2 = [Measure]-4

Measure 3 = IF(MAX(Sheet1[Index])>=[Measure 2]&&MAX(Sheet1[Index])<[Measure],1,0)

     

    Best Regards

    Maggie

v-juanli-msft's avatar
v-juanli-msft
Community Support
8 years ago

Hi someone

1. In query editor, create an index column from 1.

   Then create a blank query, and write code in Advanced editor.

let
    Source = Table.SelectColumns(Sheet1,{"Quarter","Index"})
in
    Source

 

2. In Relationship View, create the relationship between two tables.

 

3. In Report view, create measures, then add [Measure 3] to the Visual Filter and show 1

Measure = SELECTEDVALUE(Query1[Index])

Measure 2 = [Measure]-4

Measure 3 = IF(MAX(Sheet1[Index])>=[Measure 2]&&MAX(Sheet1[Index])<[Measure],1,0)

 

Best Regards

Maggie