Forum Discussion

Dismissile's avatar
Dismissile
Regular Visitor
9 years ago

Filter report up until the current week

I have a table that is measuring the number of searches performed on a website. I created a report that lists the following:

 

  • Term
  • # Searches

 

I added a visual level filter on my Date to only show data that is in the last 7 days (not including today).

 

I need to be able to create the same exact visual in the same report to show the top 10 searches of all time - but I do not want it to include this week's data.

 

Is there any way to do this?

3 Replies

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    9 years ago

    Hi Dismissile,

    Please create a new table by clicking "New Table" under Modeling on Home page. Get the top10 search of all time excluding this week.

    NewTable=TOPN(10,FILTER(Table,Table[Date])<=DATEADD(Today(),-7,Day)),count(table[research]))


    Create a relationship between the NewTable and your fact table, select the NewTable[research] in your visual, and check if it works.

    If this is not what you want, please share your sample table for further analysis.

    Best Regards,
    Angelia

    • Dismissile's avatar
      Dismissile
      Regular Visitor
      9 years ago

      Can you re-check your syntax here? I'm not getting this thing to "compile".

       

      My tables in my model that are relevant are:

       

      Date[Date]

       

      Requests[Term]

      Requests[Search Count] (measure)

      Requests[Date]

       

      There is a relationship between the date dimension and requests table.

      • v-huizhn-msft's avatar
        v-huizhn-msft
        Microsoft Employee
        9 years ago

        Hi Dismissile,

        Could you please post your sample table or .pbix file and list the expected result for further analysis? My solution posted above didn't work?

        Best Regards,
        Angelia