Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Dismissile
Regular Visitor

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 3
v-huizhn-msft
Employee
Employee

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

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.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.