Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
tsdick
Resolver I
Resolver I

Filter Pane Collective Logic

Hello Members, I am hoping there is a simple answer to the following question. 

 

Do the pbi filters on the filters pane function together as conjunctioned filters (AND) or disjunctioned filters (OR)?

 

I have a pbi built using cube data.  The filter collective on the filters pane seems at times to be conjunctive and at times to be disjunctive.  I have page filters set up using both measures and dimensions in the same filter pane.  I am not getting the conjucntive result that I am looking for.

 

Any assistance would be appreciated.

 

Thanks.

9 REPLIES 9
v-yueyunzh-msft
Community Support
Community Support

Hi , @tsdick 

In the "Filter" on the right in Power BI Destop, certain field names are generally placed, and these placed fields are calculated using "AND" logic. The purpose of the Filter is to modify the page's filtering context to calculate the data results under different situations.
The "OR" logic can only be set for a single field, for example, the multi-selection of the slicer is joined by the "OR" logic, or the "OR AND" logic is changed in the "Advanced filtering" in the "Filter Pane" to filter a single field,like this:

vyueyunzhmsft_0-1668051026793.png

This means the [Column1] can equals 1 or 2 .

 

Best Regards,

Aniya Zhang

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

 

Hello Aniya, thanks for the detailed explanation.  My understanding is/was just as you explained. 

 

However my experience w/ the following example filter result is different from my understanding.  In the following PBI example (before and after filtering) I am trying to filter out (exclude) rows where both the Rev $ = $0 AND the COS $ = $0 (the data source is a cube and Rev/COS are both measures in the cube).  In other words I am trying to include rows where either the Rev $ <> $0 OR the COS <> $0.   The before image below shows rows before filtering and the 2 after images below show the rows after 2 filter settings.  The before image is good, the after 1 image is good, but I don't understand why the after 2 image has filtered out all rows when my filter logic should produce a result w/ 13 rows (no image provided).

 

Before

tsdick_0-1668090305735.png

 

After 1 (Rev $<> $0)

tsdick_1-1668090719191.png

 

After 2 (Rev $<> $0 AND COS $ <>$0)

tsdick_2-1668090835908.png

 

Hi , @tsdick 

I check your shared picture. I think it works in logic.

The (Rev $<> $0 AND COS $ <>$0) means when both Rev$<> $0 and COS$<>$0 conditions are met.

In your After1, you can see that when the Rev$<> $0 condition is satisfied, there is no COS$<>$0 condition, so it is empty in After2.

For the "AND" logic, it represents a case where two conditions are met at the same time, which can be understood as an intersection.

 

Best Regards,

Aniya Zhang

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

Hello Aniya, my understanding is that w/ the Rev $<> $0 AND COS $ <>$0 conditions both Rev and COS have to be $0 to exclude these rows from the result.  In the Before  image you will see more than several rows where the Rev and/or the COS is not $0.  All these rows should be included in the After 2 result.  

 

Your After1 observation seems incorrect.  There are rows in the Before image w/ $0 Rev and non-$0 COS.

 

Please take another look at the Before, After1, and After2 filters and results.

 

Thanks.

 

Tim  

Hi, @tsdick 

The "Rev $<> $0 AND COS $ <>$0" means when we satisfy Rev$!=0 and satisfy Cos$!=0 at the same time, we can see all Cos$=0 when Rev$!=0 is in the case according to your "After 1". So when you filter "Rev$<> $0 AND COS$ <>$0" there will be empty.

If you want to filter " Rev $ = $0 AND the COS $ = $0". You can try to select the "Rev $" is 0 and "COS $" is 0.

 

Best Regards,

Aniya Zhang

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

Hello Aniya, thanks for taking the time to reply.  My comments...

 

The Before image shows rows w/ Rev <> $0 AND w/ COS <> $0.  Therefore the After 2 image should show at a minimum those rows w/ Rev <> $0 AND w/ COS <> $0.  However the After 2 images shows no rows (an empty set).  

 

You are correct that "Rev $<> $0 AND COS $ <>$0" should include only rows w/ both <> $0.  I am trying to set filters that would apply Rev $<> $0 OR COS $ <>$0.  I am thinking that this is not possible using the PBI filter pane filtering and its conjunctive logic.  

 

Thanks again for you help.

 

Tim

Hi, @tsdick 

You are right .Only for the oringle filter pane, it cannot be realized your need "Rev $<> $0 OR COS $ <>$0".

For your need , I have a method for you , you can try .

Here is my test data :

vyueyunzhmsft_0-1668561327647.png

We can create a measure :

Flag = IF([Rev $] <> 0 || [COS $] <> 0 ,1,0)

Then wen put this measure on the "Filter on this visual" and configure it , then we will meet your need :

vyueyunzhmsft_1-1668561384731.png

 

Best Regards,

Aniya Zhang

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

 

Hello Aniya, I understand your point -- I had the same thought.  However, my data source is a cube using SQL Server Analysis Service database as the Get Data means.  PBI inhibits the creation of measures, columns, DAX, etc, etc when data is from the cube source.

 

Any other ideas?

 

Thanks.

 

Tim

Hi, @tsdick 

Ah... You mean in your situation, it can not create measure.

So just for the filter pane, i find no way to get the "OR" logic.And your understand is right!

Thank for your sharing and your time.

 

Best Regards,

Aniya Zhang

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

 

 

Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.