Forum Discussion
Merge selection from two slicers
Hello to all,
I am puzzled by one task and will be very glad if you could give a hint.
For simplification of the situation, let's have two tables - weekly sales and weekly brand awareness.
| Brand | Week | Sales |
| Alpha 1 | Week 1 | 204 |
| Alpha 2 | Week 1 | 39 |
| Alpha 3 | Week 1 | 28 |
| Beta 1 | Week 1 | 291 |
| Beta 2 | Week 1 | 208 |
| Beta 3 | Week 1 | 295 |
| Brand | Week | Product | Awareness |
| Alpha 1 | Week 1 | Coffee | 42% |
| Alpha 2 | Week 1 | Coffee | 43% |
| Alpha 3 | Week 1 | Coffee | 41% |
| Beta 1 | Week 1 | Coffee | 66% |
| Beta 2 | Week 1 | Coffee | 63% |
| Beta 3 | Week 1 | Coffee | 9% |
| Alpha 1 | Week 1 | Tea | 52% |
| Alpha 2 | Week 1 | Tea | 16% |
| Alpha 3 | Week 1 | Tea | 69% |
| Beta 1 | Week 1 | Tea | 24% |
| Beta 2 | Week 1 | Tea | 9% |
| Beta 3 | Week 1 | Tea | 27% |
Alpha1, Alpha 2, Alpha 3 are brands of mother company.
Beta 1, Beta 2, Beta 3 are competing brands.
Each of these groups of brands are included to separate slicers (Alpha - in Alpha slicer, Beta - in Beta slicer) with single selection for each.
Having brands in different slicers allows building the first two charts but doesn't allow to make the last two charts (see picture below).
I am trying to find a way to somehow merge the selections made in Alpha & Beta slicers in order not to make one more slicer for the third and the fourth charts.
Is there any way to do so? I was trying to implement SELECTEDVALUE, tried to make calculated columns in raw data - it doesn't work, alas.
Many thanks for your answer in advance.
hi Anonymous
(Alpha - in Alpha slicer, Beta - in Beta slicer)
since Alpha and Beta and in the same column, why do you want to keep them in two separate slicers?
Do you have set edit interaction for them to get it?
If so, I would suggest you put them in one slicer not in two slicers, in your current way, it could not get the last two charts.
If you still want to keep them in two separate slicers, you need two separate dim brand table, which is for Alpha and another for Beta.
then create measure as below:
Alpha sales = CALCULATE(SUM('weekly sales'[Sales]),FILTER('weekly sales','weekly sales'[Brand] in VALUES(Alpha[Brand])))Bate sales = CALCULATE(SUM('weekly sales'[Sales]),FILTER('weekly sales','weekly sales'[Brand] in VALUES(Beta[Brand])))sum sales = CALCULATE(SUM('weekly sales'[Sales]),FILTER('weekly sales', 'weekly sales'[Brand] in VALUES(Alpha[Brand])||'weekly sales'[Brand] in VALUES(Beta[Brand])))Of course, it has the same logic for weekly brand awareness table.
here is sample pbix file, please try it.
Regards,
Lin
3 Replies
Anonymous , which slicer you want to avoid. Not very clear. It seems you already have a common Brand and week dimension. and that is joined to both tables.
Table / Dimension - Brand, Week
Tables /Fact - Table 1, Table 2
Dear amitchandak Thanks for your reply.
I have a report where I have a control panel at the top, then two sales charts, and finally, awareness charts. On my control board I have two separate slicers (one - for Alpha brands, second - for Beta brands). I select one brand from each group (for example, Alpha 2 in Alpha slicer, Beta 1 in Beta slicer) and easily make separate sales charts, as shown in my example picture).
Yet, I can't make awareness charts, because each slicer filters each group and they don't intersect. Of course, I can make additional slicer with multiple selection after sales charts and manually select there Alpha 2 & Beta 1, but I want to avoid it.
So I was thinking of some solution which can: 1) understand, what is selected in each of Alpha and Beta slicers 2) combine selection of Alpha & Beta slicers 3) use this selection as a pre-filter for awareness charts.
Hope I made the issue clearer.
hi Anonymous
(Alpha - in Alpha slicer, Beta - in Beta slicer)
since Alpha and Beta and in the same column, why do you want to keep them in two separate slicers?
Do you have set edit interaction for them to get it?
If so, I would suggest you put them in one slicer not in two slicers, in your current way, it could not get the last two charts.
If you still want to keep them in two separate slicers, you need two separate dim brand table, which is for Alpha and another for Beta.
then create measure as below:
Alpha sales = CALCULATE(SUM('weekly sales'[Sales]),FILTER('weekly sales','weekly sales'[Brand] in VALUES(Alpha[Brand])))Bate sales = CALCULATE(SUM('weekly sales'[Sales]),FILTER('weekly sales','weekly sales'[Brand] in VALUES(Beta[Brand])))sum sales = CALCULATE(SUM('weekly sales'[Sales]),FILTER('weekly sales', 'weekly sales'[Brand] in VALUES(Alpha[Brand])||'weekly sales'[Brand] in VALUES(Beta[Brand])))Of course, it has the same logic for weekly brand awareness table.
here is sample pbix file, please try it.
Regards,
Lin
