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
Jayne_B
Frequent Visitor

Filtering in Matrix

Hello,

 

I'm very new to Power BI and struggling with a filter; I've a matrix table where I'm showing a set of sites and the % completion of activity over the last 5 weeks (the weeks is a dynamic filter set to last 5 weeks, and the % complete is a calculated field based off a count).

 

I'd want to filter down to 0% for my current week (but still have it show the % complete for the last 4 weeks reagardless of result). However, when I filter to less that 1 on completion %, the filter applies to all weeks in view. Any guidance on how I can overcome this so the report stays dynamic week on week?

 

Example:

Site23456
Site A100%0%100%95%4%
Site B100%100%0%100%0%
Site C100%100%100%0%100%
Site D0%0%100%100%50%

 

Using the example above the filter I'd like o apply would just show me site B and not any value where is less 100%.

 

Thank you 

1 ACCEPTED SOLUTION
v-robertq-msft
Community Support
Community Support

Hi, @Jayne_B 

According to your description and example, I think you want to just apply a filter on the percentage of Sites that are only in the current week(in my case is Week 6), right? You can try my steps:

  1. This is the test data I transformed based on your Matrix:

v-robertq-msft_0-1612770259618.png

 

  1. I created a calculated table to place into the Slicer, like this:
Slicer = GENERATESERIES(0,1.05,0.05)

v-robertq-msft_1-1612770259652.png

 

  1. I created a measure:
Percent1 =

var _currentweek=MAXX(ALL('Table'),[Week])

return

IF(

    MAX([Week])<>_currentweek,MAX([Percent]),

    IF(MAX([Percent])>=MIN('Slicer'[Value])&&

    MAX([Percent])<MAX('Slicer'[Value]),MAX([Percent]),

BLANK()))
  1. I placed this measure into the Matrix to replace the original [Percent] field, like this:

v-robertq-msft_2-1612770259657.png

 

And I guess this can be what you want:

v-robertq-msft_3-1612770259666.png

 

You can download my test pbix file here

 

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-robertq-msft
Community Support
Community Support

Hi, @Jayne_B 

According to your description and example, I think you want to just apply a filter on the percentage of Sites that are only in the current week(in my case is Week 6), right? You can try my steps:

  1. This is the test data I transformed based on your Matrix:

v-robertq-msft_0-1612770259618.png

 

  1. I created a calculated table to place into the Slicer, like this:
Slicer = GENERATESERIES(0,1.05,0.05)

v-robertq-msft_1-1612770259652.png

 

  1. I created a measure:
Percent1 =

var _currentweek=MAXX(ALL('Table'),[Week])

return

IF(

    MAX([Week])<>_currentweek,MAX([Percent]),

    IF(MAX([Percent])>=MIN('Slicer'[Value])&&

    MAX([Percent])<MAX('Slicer'[Value]),MAX([Percent]),

BLANK()))
  1. I placed this measure into the Matrix to replace the original [Percent] field, like this:

v-robertq-msft_2-1612770259657.png

 

And I guess this can be what you want:

v-robertq-msft_3-1612770259666.png

 

You can download my test pbix file here

 

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-robertq-msft 

 

Thank you for the detail, that's been really useful, I've been able to replicate what you've outlined and it's working. 

It is possible to take this one step further? The filter to less than 100% is great, I'd like the table to show me the sites which only then fall into that filter.

Is that possible to do ?

Hi, @Jayne_B 

According to your description, I guess that you want to make the Matrix only display the sites that percentage is not blank in the current week, right?

After the test, I think it’s impossible if I’m trying to use a measure or calculated column to achieve this because the measure can’t be placed as the Axis or Legend of a Matrix and the calculated column can’t be affected by Slicer in Power BI. As a result, I suggest you to use the visual filter to achieve the expected result manually. You can filter the sites with no blank value manually like this:

v-robertq-msft_0-1613529881179.png

 

And I guess this can be what you want.

You can download my test pbix file here

 

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.