Forum Discussion
Power BI Visual
- Anonymous2 years ago
Hi alir22456 ,
ryan_mayu Thanks for your reply!
And alir22456 you can try this way:
Here is my sample data:Use this DAX to create two calculated tables for slicers (If only one slicer won't do what you need it to do):
Buy = VALUES('Table'[Services])Not Buy = VALUES('Table'[Services])Please note that there is no relationship between tables:
Use this DAX to create a measure to return Customers who have only bought the Services selected in the first slicer:
Buy_single_Customers = CONCATENATEX( FILTER( 'Table', 'Table'[Services] IN VALUES(Buy[Services]) ), 'Table'[Customers], "," )Continue to use this DAX to create a measure that returns Customers who bought both the Services selected in the first slicer and the Services selected in the second slicer:
Buy_both_Customers = VAR _BUY = CALCULATETABLE( DISTINCT('Table'[Customers]), FILTER( ALL('Table'), 'Table'[Services] IN VALUES(Buy[Services]) ) ) VAR _NOT_BUY = CALCULATETABLE( DISTINCT('Table'[Customers]), FILTER( ALL('Table'), 'Table'[Services] IN VALUES('Not Buy'[Services]) ) ) VAR _Both = INTERSECT(_BUY, _NOT_BUY) RETURN CONCATENATEX( _Both, 'Table'[Customers], "," )Finally this DAX is used to create a measure to return Customers who only bought the Services selected in the first slicer and not the Services selected in the second slicer:
Both - Single = VAR _BUY = CALCULATETABLE( DISTINCT('Table'[Customers]), FILTER( ALL('Table'), 'Table'[Services] IN VALUES(Buy[Services]) ) ) VAR _NOT_BUY = CALCULATETABLE( DISTINCT('Table'[Customers]), FILTER( ALL('Table'), 'Table'[Services] IN VALUES('Not Buy'[Services]) ) ) VAR _Both = EXCEPT(_BUY, _NOT_BUY) RETURN CONCATENATEX( _Both, 'Table'[Customers], "," )And the final output is as below:
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi ryan_mayu
Thanks but the data I showed is just for understanding. I have a huge dataset so creating pages seperately will not be a practical solution. And the filter is applied through a slicer. I cannot figure out a way to create a DAX measure that filters the results without using a slicer that dynamically updates the result based on the selected option. Is there a way to subtract the list values of the table visuals (not the actual table) and get the results.
Thanks
or you can create a mapping table and filter this table to get the corresponding output.
| Plan | Customer | | ------ | ---------- | | Plan 1 | Customer 1 | | Plan 1 | Customer 2 | | Plan 1 | Customer 3 | | Plan 1 | Customer 4 | | Plan 2 | Customer 3 | | Plan 2 | Customer 4 | | Plan 2 | Customer 5 |