Forum Discussion

tkrupka's avatar
tkrupka
Resolver II
7 years ago
Solved

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 Mont...
Need Help
  • tkrupka's avatar
    tkrupka
    7 years ago

    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:

     

    Thanks Greg_Deckler for leading me down the right path.

     

tkrupka's avatar
tkrupka
Resolver II
7 years ago
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's avatar
tkrupka
Resolver II
7 years ago

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:

 

Thanks Greg_Deckler for leading me down the right path.