Forum Discussion
FILTER - AND operator
- Anonymous2 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 __resultUsing 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 TeamIf 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 , 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
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)?
- Anonymous2 years agoNot applicable
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 TeamIf 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!- Kev592 years agoHelper I
Anonymous
understood 😉
not possible to don't use the relationship directly the measure calculation?