Forum Discussion

RichFlorida's avatar
RichFlorida
Icon for Helper V rankHelper V
2 years ago
Solved

Page filter with two conditions

I have the following SQL statement with an OR condition impacting two different columns:

 

 

Select *
From tableA
Where (DeptName is not Null OR DivisionName is not Null)

 

 

How do I add this where close condition as a "Page FIlter" in PowerBI Desktop.

 

Thanks!

Need Help
  • adudani's avatar
    adudani
    2 years ago

    hi RichFlorida ,'

    there is "AND" / "OR" selection right under null.

     

    select "And" , drop in the other required field and condition similar to the steps above.

     

  • manvishah17's avatar
    manvishah17
    2 years ago

    Hi RichFlorida ,
    You can try this measure

     

    IncludeMeasure = IF(NOT(ISBLANK(MAX([DeptName]))) || NOT(ISBLANK(MAX([DivisionName]))), 1, 0)

     

    or 

    • Create a Calculated Column:

      IncludeRow = IF(NOT(ISBLANK([DeptName])) || NOT(ISBLANK([DivisionName])), 1, 0)

    • Add a Table Visual:

      • Go to the Report view.
      • Add a new table visual to your report.

    • Add Fields to the Table Visual:

      • Drag and drop the fields you want to display from your table into the table visual.
      • Drag and drop the IncludeRow column into the Filters pane of the visual.
      • Set the filter condition to show rows where IncludeRow is 1.

     

     

4 Replies

  • adudani's avatar
    adudani
    Icon for Memorable Member rankMemorable Member
    2 years ago

    hi RichFlorida ,

     

    please see the below image dispaying where page filters are added.

     

     

    1. Open the filters pane

    2. Filters on this page 

    3. Drag and drop the field 

    4. filter  type = advanced filtering

    5. show items when the value = is not

    6. condition

  • RichFlorida's avatar
    RichFlorida
    Icon for Helper V rankHelper V
    2 years ago

    Hi adudani 

     

    Thak you for your reply. Unfortunatly this does NOT answer my questions.

     

    I need to add a single filter having to do with TWO columns.

    The filter should exclude any records that have two values null at the same time (DeptName & DivisionName). If only one value is null, then it is ok to incude the record.

     

    Any ideas greatly apreciated!

     

     

     

    • adudani's avatar
      adudani
      Icon for Memorable Member rankMemorable Member
      2 years ago

      hi RichFlorida ,'

      there is "AND" / "OR" selection right under null.

       

      select "And" , drop in the other required field and condition similar to the steps above.

       

  • manvishah17's avatar
    manvishah17
    Icon for Solution Supplier rankSolution Supplier
    2 years ago

    Hi RichFlorida ,
    You can try this measure

     

    IncludeMeasure = IF(NOT(ISBLANK(MAX([DeptName]))) || NOT(ISBLANK(MAX([DivisionName]))), 1, 0)

     

    or 

    • Create a Calculated Column:

      IncludeRow = IF(NOT(ISBLANK([DeptName])) || NOT(ISBLANK([DivisionName])), 1, 0)

    • Add a Table Visual:

      • Go to the Report view.
      • Add a new table visual to your report.

    • Add Fields to the Table Visual:

      • Drag and drop the fields you want to display from your table into the table visual.
      • Drag and drop the IncludeRow column into the Filters pane of the visual.
      • Set the filter condition to show rows where IncludeRow is 1.