Forum Discussion

taniabyrnes's avatar
taniabyrnes
New Member
9 years ago
Solved

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,...
Need Help
  • ankitpatira's avatar
    ankitpatira
    9 years ago

    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.

     

taniabyrnes's avatar
taniabyrnes
New Member
9 years ago

Hi Anonymous 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 excelcriteria

ankitpatira's avatar
ankitpatira
Icon for Community Champion rankCommunity Champion
9 years ago

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. 

  • taniabyrnes's avatar
    taniabyrnes
    New Member
    9 years ago

    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.

     

    • ankitpatira's avatar
      ankitpatira
      Icon for Community Champion rankCommunity Champion
      9 years ago

      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.

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      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