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
Anonymous
Not applicable

How can I limit my results in a Matrix

Good day!

 

I have a matrix where I'm returning scheduled hours for employees (please see screenshot below)

 

I have a date table defined as below:

TableDT = 
VAR MinYear = YEAR ( MIN ('CW_Scheduled Hours'[Date] ) )
VAR MaxYear = YEAR ( MAX ('CW_Scheduled Hours'[Date] ) )
RETURN
ADDCOLUMNS (
FILTER (
CALENDARAUTO( ),
AND ( YEAR ( [Date] ) >= MinYear, YEAR ( [Date] ) <= MaxYear )
),
"Calendar Year", "CY " & YEAR ( [Date] ),
"Month Name", FORMAT ( [Date], "mmmm" ),
"Month Number", MONTH ( [Date] ),
"Week Number", WEEKNUM ( [Date] ),
"Year Number", YEAR ( [Date] ),
"Index", 12 * YEAR ( [Date] ) + MONTH ( [Date] ))

 

I also have a calculated measure (thanks to the help of another user) to provide my column headers by week:

Year-WeekNumber = SWITCH(
                            TRUE(),
                            [Week Number] < 10, [Year Number]  & "-0" & [Week Number],
	                    [Week Number] >= 10, [Year Number]  & "-" & [Week Number] )

 

I was wondering how I might limit the matrix values to the current week and only the next 4. Is there a way to do this without changing my SQL query that pulls the data?

THANK YOU

matrix.jpg

1 ACCEPTED SOLUTION
DataInsights
Super User
Super User

@Anonymous,

 

One approach is to create a Relative Week column in the date table, and set a filter on the visual to filter Relative Week between 0 and 4. Here's the calculated table with Relative Week:

 

TableDT = 
VAR MinYear = YEAR ( MIN ('CW_Scheduled Hours'[Date] ) )
VAR MaxYear = YEAR ( MAX ('CW_Scheduled Hours'[Date] ) )
RETURN
ADDCOLUMNS (
FILTER (
CALENDARAUTO( ),
AND ( YEAR ( [Date] ) >= MinYear, YEAR ( [Date] ) <= MaxYear )
),
"Calendar Year", "CY " & YEAR ( [Date] ),
"Month Name", FORMAT ( [Date], "mmmm" ),
"Month Number", MONTH ( [Date] ),
"Week Number", WEEKNUM ( [Date] ),
"Year Number", YEAR ( [Date] ),
"Index", 12 * YEAR ( [Date] ) + MONTH ( [Date] ),
"Relative Week",
VAR vToday = TODAY()
VAR vWeekEndDate =
    [Date] - WEEKDAY ( [Date], 1 ) + 7
VAR vWeekEndDateToday =
    vToday - WEEKDAY ( [Date], 1 ) + 7
VAR vResult =
    CONVERT ( ( vWeekEndDate - vWeekEndDateToday ) / 7, INTEGER )
RETURN
    vResult
)

 

DataInsights_0-1662157985582.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

3 REPLIES 3
Anonymous
Not applicable

I apologize for the late reply. I wound up coming down with COVID! 
So, this works perfectly. I'm going to accept as solution. I was going to post an ask to help me walk me through this but when I looked up the WEEKDAY function and then plugged in numbers, I saw the logic. Thank you!

@Anonymous,

 

Hope you've fully recovered from COVID. Glad to hear the solution works!





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




DataInsights
Super User
Super User

@Anonymous,

 

One approach is to create a Relative Week column in the date table, and set a filter on the visual to filter Relative Week between 0 and 4. Here's the calculated table with Relative Week:

 

TableDT = 
VAR MinYear = YEAR ( MIN ('CW_Scheduled Hours'[Date] ) )
VAR MaxYear = YEAR ( MAX ('CW_Scheduled Hours'[Date] ) )
RETURN
ADDCOLUMNS (
FILTER (
CALENDARAUTO( ),
AND ( YEAR ( [Date] ) >= MinYear, YEAR ( [Date] ) <= MaxYear )
),
"Calendar Year", "CY " & YEAR ( [Date] ),
"Month Name", FORMAT ( [Date], "mmmm" ),
"Month Number", MONTH ( [Date] ),
"Week Number", WEEKNUM ( [Date] ),
"Year Number", YEAR ( [Date] ),
"Index", 12 * YEAR ( [Date] ) + MONTH ( [Date] ),
"Relative Week",
VAR vToday = TODAY()
VAR vWeekEndDate =
    [Date] - WEEKDAY ( [Date], 1 ) + 7
VAR vWeekEndDateToday =
    vToday - WEEKDAY ( [Date], 1 ) + 7
VAR vResult =
    CONVERT ( ( vWeekEndDate - vWeekEndDateToday ) / 7, INTEGER )
RETURN
    vResult
)

 

DataInsights_0-1662157985582.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.