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 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.
Thanks Anonymous