Show data in secondary table only when item selected from primary table

[Anonymous]

Hello,

I have a matrix showing cost by a column called [SubCategory]

You can expand [SubCategory] to show detailed data from a column called [Business Process]

I have a second table which just shows rate and volume. I don't want the values of these to appear, unless someone has selected a [Business Process] because at the [SubCategory] level, rate and volume summed up is pointless. 

 

Thanks 🙂

[moritzmassimo]

Hi @Anonymous,
I really like your solution!
There is just one thing, what I'm not able to solve:
If the user then wants to select one of the items in the "new" table, the table disappears again, because the selection from the other table is gone. (If you don't click CTRL while selecting)
I guess you have come to that issue aswell, did yu find a solution for it?
Best,
Moritz

[amitchandak]

You can use isfiltered or isinscope

https://powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/

[Anonymous]

There is no way to make a table disappear but I use this trick for these situations
Create two measures:

CARD MESSAGE =
IF(
   HASONEVALUE(
                             Table[Business Process]
                            ),
"",
"Click on a business process to show rate and volume"
)

Make Transparent =

IF (

HASONEVALUE( Table[business process]),

"#FFFFFF00",

"White"

)

You can then cover the table with a card, put the [Card Message] measure in the value of the card and the [Make Transparent] measure in the conditional formatting of the background (click on the three dots in the background color submenu to insert a measure as conditional formatting). Then put background transparency to 0% and remove card category option

In this way, when a B.P. is selected the card will become transparent and show the values, when it isn't it will show the message

Hope this helps

[UmYil]

What do you do when you still want to be able to select in the table or export it? Now the card is on the top... 

It's a nice solution, but still need a solution for the problem above. 

[debiesven]

is the table below the card still usable when you put a card on top of it with background transparancy=0%?

[UmYil]

I've tested it, unfortunately the answer is no 😞 

[cobyiv]

Such a brilliant solution. Thanks!

[Anonymous]

In an act of total abandonment and throwing caution to the wind, I wrote the following measure:

Rate = IF(ISFILTERED('Table'[Business Process]),SUM('Table'[Rate]))

It seems to work. It remains blank unless specifically a Business Process is selected on my report, then it shows the corresponding rate. I have to have the table broken out by month, else it shows a sum of all month rates, but that's ok. 

I can't believe I accidentally wrote a measure that worked!

[Anonymous · Accepted solution]

In an act of total abandonment and throwing caution to the wind, I wrote the following measure:

Rate = IF(ISFILTERED('Table'[Business Process]),SUM('Table'[Rate]))

It seems to work. It remains blank unless specifically a Business Process is selected on my report, then it shows the corresponding rate. I have to have the table broken out by month, else it shows a sum of all month rates, but that's ok. 

I can't believe I accidentally wrote a measure that worked!

[Anonymous]

This is ok but it will show a blank table by default. Try the card trick if you want the report to be more user-friendly and have the table with no data disappear

However if it's for personal use it's fine that way. Keep in mind, though, that ISFILTERED will work also if the user uses CTRL to select multiple business processes. You should use HASONEVALUE if you need to show the numbers only when a single BP is selected. If not, you're good