Forum Discussion

Kev59's avatar
Kev59
Helper I
2 years ago
Solved

FILTER - AND operator

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 show...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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!

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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!