How to get slicer of one table to affect another table

[Anonymous]

Hi!

I have now included a pbix file to make it easier to understand what I want. Please help!
https://www.dropbox.com/s/3bfkfjfpk61jybg/Test.pbix?dl=0

All I want to do is display the sum of columns, A,B,C in a bar chart that can be filtered by the region, that's it. If you do it the normal way, the bars become squashed (First bar chart).
If you do the workaround by creating another table (TABLE X), I don't know how to get the region filter to affect it.

Here is what I tried originally:

I have a table, table Y:

TABLE Y
Opportunity  Date   Region  A  B  C
1246              26/11   UK       1   0  0
1212              23/12   USA     1   1  0
5333              12/08   UK       0    0 1

I also have a created table that pulls the sum of column a, b, c from TABLE Y that are not older than 2 years:

TABLE X
Columnheaders     Sum

A                             10
B                             50
C                            70

To find the sum I'm using this formula:

switch(
Columnheaders,
"A",
calculate(sum(TABLEY[A]),
TABLEY[DATE]>=date(YEAR(NOW())-1,1,1)
etc
This actually works fine and correctly filters out opporunities older than 2 years.

The problem comes when I try to get a slicer of the REGION column in TABLE Y to affect TABLE X.

I've tried adding SelectedValue to the formula as so:
switch(
Columnheaders,
"a",
calculate(sum(TableY[a]),
TableY[DATE]>=date(YEAR(NOW())-1,1,1)
TableY[REGION]=SELECTEDVALUE(TableY[REGION]))

But when I add that bit, the bar disappears!
I don't understand. If I select 'UK' on the slicer, selectedvalue=UK and so surely it should just filter out the rows where region is not UK.
How can I alter the formula to get the slicer to affect TABLE X?

Thanks

[Anonymous · Accepted solution]

OK so I figured it out by digging through the forums.

The problem is the switch formula was a calculated column and these can't be affected by Filters. The reason is they are calculated pre-visualisation. You have to turn the calc column into a measure.

So I just put a 'min' around the first part of the switch forumla and made it a measure. Now it works with the filters.

[amitchandak]

@Anonymous , not very clear. But if you are filtering using the column to the table, I do not think you need a region filter

calculate(sum(Table2[a]),
Table2[DATE]>=date(YEAR(NOW())-1,1,1))

[Anonymous]

Ah sorry, how can I make it more clear?
The issue is the region column only exists in Table1. But I am making the bar chart out of Table2, which pulls in data from Table1 using the switch&calculate formula above.
This means if I select something in the slicer, it doesn't affect the bar chart, because it's only filtering table1. Hence needing to include it in the switch/calculate formula somehow, same way I did for the date.

[VIJAYKUMART]

selected value formulae should be written to the table (column) ur using the slicer. 

[Anonymous]

Sorry, I don't understand what you mean. How can I change the formula to get the slicer of TABLE Y to affect TABLE X?

[VIJAYKUMART]

Table Y is dimenstion table and Table X is fact table connected via relationship. it will work.. 

or simply share sample pbix file without sensitive information. it helps to solve problem more quickly by BI Community members... 

[Anonymous]

There is no relationship between the tables. There cannot be, as TABLE X only has two columns, 'Column headers' and 'Sum'. Neither of these can be linked to columns in TABLE Y. I've given an example of what table Y looks like above...

[VIJAYKUMART]

please try with ALLSELECTED:

switch(
Columnheaders,
"a",
calculate(sum(TableY[a]),
TableY[DATE]>=date(YEAR(NOW())-1,1,1),
TableY[REGION]=ALLSELECTED()

if still not works.. share sample pbix in community

[Anonymous]

Unfortunately, that breaks the formula. It says 'A table of multiple values was supplied where a single value was expected'.

[VIJAYKUMART]

k.. may be issue with understanding the requirement. share pbix in community.. 

go on trail and error method until it solves.. keep trying🙂

[Anonymous]

OK!

I have created a pbix file. I just want the second bar chart to be able to be filtered by the region filter.
The first bar chart just shows what happens if I drag multiple columns into one bar chart- it doesnt look right, which is why I created the second table.

Thanks for helping!