cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
tkrupka Regular Visitor
Regular Visitor

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.


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

View solution in original post

tkrupka Regular Visitor
Regular Visitor

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.


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

View solution in original post

tkrupka Regular Visitor
Regular Visitor

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

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])))

 

tkrupka Regular Visitor
Regular Visitor

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
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors