cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
heytherejem
Post Prodigy
Post Prodigy

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

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. 

 

PBI Example.png 

Thanks 🙂

1 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!

View solution in original post

7 REPLIES 7
moritzmassimo
Frequent Visitor

Hi @Agherardi,
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
Super User
Super User

You can use isfiltered or isinscope

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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
Agherardi
Resolver I
Resolver I

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

Such a brilliant solution. Thanks!

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!

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!

View solution in original post

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

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors