cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bkreutz
Frequent Visitor

Measure Filter not filtering Matrix Visual columns

Hello,

 

I am having an issue with the Matrix visual. The matrix has Forecast Group as rows and Years (which can change over time) as columns. I created a measure that determines if a year is within a selected date range on an unrelated table. 

 

For example, if the selected date range was from 2019-2021, the "Max BinaryDate" measure would output "1" for all years between 2019 and 2021 and would output "0" for all years outside this date range.

 

I am trying to use this measure to filter out the columns of the matrix outside the selected date range. It works perfectly if the "Table" visual is used. I have selected years 2021-2023 in the below table and as you can see, only 2021 through 2023 are showing up:

bkreutz_1-1619216236208.png

When I convert this table to a matrix with the years as the columns, we can see that the "Max BinaryDate" measure is calculating the correct number (1 or 0) depending on the selected fiscal year. However, when I try to filter "Max BinaryDate", it does not remove columns that are not "1". Also something interesting is that when I filter Max BinaryDate = 0, the entire table gets filtered out.

bkreutz_0-1619216008469.png

 

Any idea why this might be happenning and how to fix it?

 

For reference, the Max BinaryDate measure is: 

IF(

countrows(

values('Utilization Categories'[Concat FcastGroup])) = 1

,[BinaryDate]
,MAXX(values('Utilization Categories'[Concat FcastGroup]), [BinaryDate])
)
 
and [BinaryDate] is another measure with [MaxDate] and [MinDate] being the selected date range
If(
OR(
Max('Utilization Categories'[Fiscal Year])>[MaxDate],
Min('Utilization Categories'[Fiscal Year])<[MinDate]
)=TRUE
,0,1)
 

Man, I wish Power BI supported dynamic calculated columns cuz that would make this so much easier haha!

 

Thanks in advance!

Brandon

 

2 REPLIES 2
v-luwang-msft
Community Support
Community Support

Hi @bkreutz ,

Could you pls share your pbix file ?Remember to remove confident data.

 

Best Regards

Lucien

Hi Lucien,

 

I'll try to get you a file with confidential data removed later today. Thanks in advance!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors