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
taniabyrnes
New Member

Multi Level Filter

I have a matrix, I'd like to be able to exclude all rows that have a zero value in Sep 16, Oct 16 AND Nov 16. The filters seem to work on their own and you can add an and/Or Filter within each month, but not accross all months. Am I missing something?

 

To clarify, I want to look at all records that have a value in any of those three months, if its in another month, exclude it. eg. in the table below, Joshua On should be excluded, as his due date falls outside the months I am wanting to look at

 

Tania

Sort.JPG

1 ACCEPTED SOLUTION

@taniabyrnes I see your problem. Way to overcome that is in power bi desktop go to query editor -> then add conditional column under Add column tab as shown below -> then in page-level filter use that conditional column to be equal to 1.

 

Capture.PNG

View solution in original post

7 REPLIES 7
CheenuSing
Community Champion
Community Champion

@taniabyrnes

 

While the solution by @ankitpatira requires that every time you have to change the page level filter when you go by different months. From Sep 16 to Oct 16 to Nov 16 to Dec 16 to Jan 17 etc. 

Which in my opinion would be cumbersome and you have to remember this.  

 

The alternative suggested by me will work irrespective the month you are in for the next three months from thereon,

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
CheenuSing
Community Champion
Community Champion

Hi @taniabyrnes

 

1. What is your data model ?

2. What criteria you use to build the three months of the report? is it based on a slicer of 3 months ??

 

Please clarify.

 

Cheers

 

CheeenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Hi @CheenuSing thank you for your reply. Im afraid Im not sure I understand your questions though, sorry. The source data is a spreadsheet that has many months when a clients account may be due. I only want to look three months ahead. I have all the data in Power BI. The criteria to create the table is also shown below. Does that answer your questions adequately?

query from created from excelquery from created from excelcriteriacriteria

@taniabyrnes What you've got is almost correct except for the filters instead of having 'is not blank' change it to 'is greater than' and make it greater than 0. 

@ankitpatira thank you for your reply, but that doesn't work. As soon as I do that, it filters out every record except those that have a value in November.

 eg. For November I chose that, but I want it to filter to show all records that have a greater than 1 value in Sep, Oct OR Nov.

 

nov.JPG

@taniabyrnes I see your problem. Way to overcome that is in power bi desktop go to query editor -> then add conditional column under Add column tab as shown below -> then in page-level filter use that conditional column to be equal to 1.

 

Capture.PNG

Hi @taniabyrnes

 

Try this

 

1. Add a column called DueMonth = Month(Table[ColumnContainingMonth&Year]) 

    From Your screen shot the column name containing Value like 'Sep16' etc

2. Create a column called 

        MonthDif = If([DueMonth] >= Month(today()) && [DueMonth] <= Month(today()) + 2,[DueMonth] -Month(today()) + 1,0)

       This will create a column with values ranging from 1 to 3 and ignoring months before and beyond three months of current             month.

3. Now create the MAtrix Report

  with Rows as Client Name ,  columns as Table[ColumnContainingMonth&Year], Values as Amount

4. On the Visual Filter set Amount is greater than zero

5. On the visual filter add MonthDif and st it to greater than equal to 1.

 

It should work

 

If this solves your issue please accept it as solution and also give KUDOS.

 

Cheers

 

CheenuSing

 

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

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.