FILTER - AND operator

[Kev59]

Dear all

I'm facing to a difficulty with my PBI report. 

I use filters (segments) to show customers and revenues based on a period. Work fine:

If I select different products, the matrix shows all the customers who ordered one of these products.

Now I would like to show the customers that ordered ALL the products selected and not one of them. 

How can I do that?

Thanks in advance

[Kev59]

Hi Both

Sorry I can't share easily my pbx (not allowed) and to long to create a new one without confidential data.

Currently, I have tried a short code:

CustomerProductCount = CALCULATE(
    DISTINCTCOUNT('CDD - Invoiced Sales'[Material Code]),
    ALLEXCEPT('Products', 'Products'[Product])
)

If I create a matrix with my accounts in X axis ('Account Grouping'[Sold_To]) and the measure above as value, I have the list of accounts like below:

In this case, I selected 6 different products in my filter.

I would like to see only the accounts using ALL the products selected (6 products in the test above).

I tried also the following measure but it doesn't work propertly:

measure = 
VAR SelectedProductsCount = COUNTROWS(VALUES('Products'[Product]))
VAR CustomerProductCount = CALCULATE(
    DISTINCTCOUNT('CDD - Invoiced Sales'[Material Code]),
    ALLEXCEPT('CDD - Account Grouping', 'CDD - Account Grouping'[Sold_to])
)
RETURN
IF(CustomerProductCount = SelectedProductsCount, 1, 0)

My final goal is to show only the accounts using ALL the products selected (X axis)  and to add in Y axis, the other products (not selected in my filter) used by these accounts.

Not sure to be clear in my wish

BR

[Ashish_Mathur]

Hi,

Prepare a small dummy dataset and for that dataset, show the expected result.

[v-huijiey-msft · Accepted solution]

@lbendlin , thanks for your concern about this case. I tried to create a sample data myself based on the user's requirement and implemented the result. Please check if there is anything that can be improved. Here is my solution:
 
 
@Kev59 , if this sample data is structurally different from the one you are using, please do not hesitate to mention it and I will modify it!

Here is the sample data I created:

Primary table:

Product table:

Customer table:

I also created a disconnected table and the final model relationship looks like this:

Put the ProductName from the disconnected table into the slicer.

Create a measure:

Measure = 
VAR __slicer = ALLSELECTED('Table 2'[ProductName])
VAR __slicer_count = COUNTROWS(__slicer)
VAR __table_count = CALCULATE( COUNTROWS( FILTER(SUMMARIZE('Table','Table'[Customer ID],'Table'[Product ID],"@count",DISTINCTCOUNT('Table'[Product ID])),[@count]>0 ) ),'Product'[ProductName] IN __slicer)
VAR __result = IF( __slicer_count = __table_count,1)
RETURN
__result

Using the matrix visual, place the CustomerID of the Customer table into Row, the ProductName of the Product table into Column, drag the ProductName of the Product table to value and select Count(distinct).

Drag Measure into the visual object level Filter and set the filter condition to "is 1".

The final visual effect is as follows:

pbix file is attached.

If you have any further questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[Kev59]

@v-huijiey-msft Sorry for the late answer, I was busy last weeks.

Your solution could work but in my case, I already use a segment (global filter between my different pages) linked to my Product table.

Is there a way to adapt your example and use a segment linked to my original table with relationship (Product table) than a segment linked to a disconnected table?

BR

[Kev59]

@v-huijiey-msft 

Many thanks for your help. Seems to be exactly what I want.

I'm going to try to adapt to my dataset.

Just a question: why do you need to create the disconnected table for products? not possible to use only the Product table (already used in my page as a slicer)?

[v-huijiey-msft]

Hi @Kev59 ,

Thank you for your reply, Ashish_Mathur .

A model relationship propagates filters applied on the column of one model table to a different model table.

Filters will propagate so long as there is a relationship path to follow, which can involve propagation to multiple tables.

A disconnected table is not intended to propagate filters to other model tables.

For more information about relationships, please see:

Model relationships in Power BI Desktop - Power BI | Microsoft Learn

If you have any further questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[Kev59]

@v-huijiey-msft 

understood 😉

not possible to don't use the relationship directly the measure calculation?

[lbendlin]

I cannot assist you if you are unable to provide sample data. I hope someone else can help you further.

[v-huijiey-msft]

Hi @Kev59 ,

Thanks for the reply from lbendlin .

Create a DAX measure that counts the number of products ordered by each customer, then compare this count to the number of products selected in the Slicer.

measure = 
VAR SelectedProductsCount = COUNTROWS(VALUES('Products'[ProductID]))
VAR CustomerProductCount = CALCULATE(
    DISTINCTCOUNT('Orders'[ProductID]),
    ALLEXCEPT('Customers', 'Customers'[CustomerID])
)
RETURN
IF(CustomerProductCount = SelectedProductsCount, 1, 0)

Add this measure to Filter to set the filter condition to only display customers with a value of 1.

I would be grateful if you could provide me with sample data for testing, please remove any sensitive data in advance.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[lbendlin]

That is an OR filter, not an AND filter. You need a measure for that.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...