Forum Discussion

joshua1990's avatar
joshua1990
Post Prodigy
2 years ago

Filter 1 table based on another table based on Article Selectio

Hi experts!

I have a report that shows 2 tables.

The first one display our sales on Article level for the selected week. The selection here happens based on a filter slicer.

Then I have a second table that shows the sales for all codes for the past 5 weeks. Here I have an offset / index column for the week that is filtered within the visual.

Now I would like to select 1 article in the first table (-via clicking on it) and then the second table is filterred on the same selection but displaying all 5 weeks.
If I click now on it just the same week is displayed in botch reports.

 

Is this possible?

Need Help

2 Replies

    • amitchandak's avatar
      amitchandak
      Super User
      2 years ago

      joshua1990 , yes disconnected date table with week rank

       

      //Date1 is independent Date table, Date is joined with Table
      new measure =
      var _max = maxx(allselected(Date1),Date1[Week Rank])
      var _min = _max  -4 //or use 5 
      return
      alculate( sum(Table[Value]), filter('Date', 'Date'[Week Rank] >=_min && 'Date'[Week Rank] <=_max))

       

       

      where for week rank create a column using YYYYWW or week start date

       

      Week Rank = RANKX('Date','Date'[Week Start date],,ASC,Dense)
      OR
      Week Rank = RANKX('Date','Date'[Year Week],,ASC,Dense) //YYYYWW format

       

      in both date tables