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
SmithyBowMassiv
Regular Visitor

FIlter Data Based On Slicer Selection

I have  data table with sales products by day for the currrent quarter [tblSales]

InvoiceDate | Product | Qty Sold

I have a table that has date period selections [tblDateSelections]

Date Period | Start Date | End Date

Within this table i have entries for Yesterday, WTD, Last Complete Week etc all with the relevant start date,end date and integer identifying the selection made

EG: Last Complete Week | 19-02-2018 | 26-02-2018 | 1

 

Within the report i have a slicer based on date selection table, i can see this working and the value change when i make a selection.

msrDatePeriod = IF(HASONEVALUE(DatePeriod[TimePeriod]),VALUES(DatePeriod[Value]),5)

 

What I want to do is create a column in tblSales that flags the relevant records based on the date selection, ideally flagging with a 1. Which i would then use as a filter criteria for visualisations. This way i can show QTD and any other date period selected.

 

Any help would be greatly appreciated.

Cheers

4 REPLIES 4
dramus
Continued Contributor
Continued Contributor

Do you have a limited number of values in the "Date Period" column?

 

If so then you can create a new column on your [tblSales] table for each value.

 

E.g. This Week = if(lookupvalue(tblDateSelections[Start Date],tblDateSelections[Date Period],"This Week") <= [InvoiceDate] && lookupvalue(tblDateSelections[End Date],tblDateSelections[Date Period],"This Week") >= [InvoiceDate],1,0)

 

Assuming you had a date range called "This Week".

This solution would only give me a 1 or 0 for that date period. I have columns within the tblSales that flag each date range already eg WTD-Flag, MTD-Flag etc. In an ideal world what I want to do is;

  • select a date range period from a slicer which is populated from a table with all the date range selections
  • this selection to then change a value in a column called MasterDate-Flag within tblSales which would update all relevant records within this column to a 1 or 0 dependant on the date period selected
  • this column would then be used as filter criteria for the report, page or visualisation

Hope this makes sense and thanks for the proposed answer.

Cheers

@SmithyBowMassiv I know many years have passed, were u able to solve this problem? any idea how?

Let me see if I can restate the problem, to see if I understand.

 

You want a column on the Sales data that shows whether this record has been selected based on the filtered values in the Dates table?

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.