cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tkrupka Resolver II
Resolver II

Filter a Matrix based on slicers/measures

I have a matrix and a couple of slicers.  

 

The slicers allow the user to pick the month and the year they want the report to end.

 

Then I have the following two measures:

Selected Report Month = SELECTEDVALUE('Calendar'[Month_Number])

Selected Report Year = SELECTEDVALUE('Calendar'[Year])

 

Now that I have my end of report setup, I have used measures to determine the start of the report:

Start Report Date Month = [Selected Report Month]+1

Start Report Date Year = [Selected Report Year] - 1

Report Start = DATE([Start Report Date Year], [Start Report Date Month], 1)

 

This give me a MM/DD/YYYY hh:mm:ss start date.

 

From here I finished my end report date:

Report End = EOMONTH([Report Start],11)+TIME(23,59,59)

 

 

So now I have the bounds of my report from Midnight on the 1st of a month until 23:59:29 on the last day of the 12th month.

 

So the question now is, how can I filter a matrix based on the [Report Start] and [Report End] measures?

 

 

My Power BI File

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User IV
Super User IV

Re: Filter a Matrix based on slicers/measures

Depends. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 

You could create a filtering measure the reports back if a row in your matrix falls within the report start and end date but the specifics of that are tough to pin down without sample data.


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

Highlighted
tkrupka Resolver II
Resolver II

Re: Filter a Matrix based on slicers/measures

Accomplished what I was trying to do.

 

 

Filter Month = IF(AND(MAX(Run_Data[Date])>=[Report Start Date Month],MAX(Run_Data[Date])<=[Report End Date]),1,2)
Filter Year = IF(AND(MAX(Run_Data[Date])>=[Report Start Date Year],MAX(Run_Data[Date])<=[Report End Date]),1,2)

Then I had to ensure that Sync Slicers was selected on the view tab. (Missed that)

 

Since I was setting the page up as static for a particular region, I was able to set up two matrix.

1st Matrix I set the Filter Month = 1

2nd Matrix I set the Filter Year = 1

 

 

Now I have:

Finished Product.JPG

 

Thanks @Greg_Deckler for leading me down the right path.

 

View solution in original post

4 REPLIES 4
Super User IV
Super User IV

Re: Filter a Matrix based on slicers/measures

Depends. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 

You could create a filtering measure the reports back if a row in your matrix falls within the report start and end date but the specifics of that are tough to pin down without sample data.


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

tkrupka Resolver II
Resolver II

Re: Filter a Matrix based on slicers/measures

My Power Bi File

 

Here is the file I am using.

 

The dropdown selections are on the first page, and the matrix is on the third page.

tkrupka Resolver II
Resolver II

Re: Filter a Matrix based on slicers/measures


@Greg_Deckler wrote:

Depends. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 

You could create a filtering measure the reports back if a row in your matrix falls within the report start and end date but the specifics of that are tough to pin down without sample data.


 

@Greg_Deckler 

 

So I was trying to figure out a way to do this.

 

I tried the following:

 

 

Report Year = FILTER(Run_Data, AND(Run_Data[Date] <= [Report Start Date Year], Run_Data[Date] >= [Report End]))

-OR-

Report End = FILTER(Run_Data, Run_Data[Date] >= [Report End])

 

 

And for both cases I get the error:

"The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

Also tried without measures:

 

Report End = FILTER(Run_Data, Run_Data[Date]>=(SELECTEDVALUE('Calendar'[Date].[Year])))

 

Highlighted
tkrupka Resolver II
Resolver II

Re: Filter a Matrix based on slicers/measures

Accomplished what I was trying to do.

 

 

Filter Month = IF(AND(MAX(Run_Data[Date])>=[Report Start Date Month],MAX(Run_Data[Date])<=[Report End Date]),1,2)
Filter Year = IF(AND(MAX(Run_Data[Date])>=[Report Start Date Year],MAX(Run_Data[Date])<=[Report End Date]),1,2)

Then I had to ensure that Sync Slicers was selected on the view tab. (Missed that)

 

Since I was setting the page up as static for a particular region, I was able to set up two matrix.

1st Matrix I set the Filter Month = 1

2nd Matrix I set the Filter Year = 1

 

 

Now I have:

Finished Product.JPG

 

Thanks @Greg_Deckler for leading me down the right path.

 

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors