showing more details when user click on some value in a visualization

[dexter]

I am using table visualization to show data along with the measures created A1 Count,A2 Count..
When i click on the A1 count column value i want to show more details for that (Showing the details of that particular count).
For example when user click on A1 Count in row2 whose value is 2, it should show the details of that two rows in second table as shown below.

ID   Pname   DeliveredDate  Level
100  Mobile  6/22/2018         A1
400  Mobile  9/19/2018         A1

The reverse should not be possible like when clicked on any row in table2 it should not change the data shown in table1. For this i tried to use Edit interactions option from Format tab shown on the ribbon but is not working as expected.

Note: Here i'm trying to give the URL like feature which when user click on any of the count shown in the table1, it should show/display more details of that level in table2 shown below the first table in the image below.

Please find the screenshot below.

The table data used for the above visualizations is shown below:

    ID        Pname     DeliveredDate  Level

100	Mobile	6/22/2018	A1
109	PC	6/23/2018	A1
188	Mobile	7/11/2018	A2
190	Laptop	8/11/2018	A2
200	TV	4/18/2018	A3
333	PC	4/24/2018	A1
377	Laptop	4/20/2018	A2
399	Mobile	4/18/2018	A3
400	Mobile	9/19/2018	A1
403	Laptop	4/22/2018	A1

[Greg_Deckler]

So, the first half should work automatically. For the second, click the second table, go to Format | Edit Interactions and click the ghost buster icon on the first table. Exit Edit Interactions.

[dexter]

It is not working as expected. Please see the screenshot below,I got two symbols(filter,none) on first table when i selected second table and clicked on Edit interactions from Format tab. When clicked on previous displayed result in table2, it is showing all the table information as you can see in table2 in the below picture..When user click on any data in Table2, i don't want it to change or interact, table2 should only be changed when clicked on Table1 data showing the appropriate data in table2..

[Greg_Deckler]

I must not be understanding something. If you don't want Table 2 to to "do anything" and I can only guess that you don't like it greying out the other rows?? Then, the fix for that is to pick a visual, just about any visual will do. Make this visual the same size as your table 2 visual. Drag it over top of your table 2 visual and while this new visual is still selected, go to Format | Bring to Top. Now, create a measure like:

Measure = BLANK()

Put this measure in your visual, get rid of any title or anything else.

Now users can't select the table.

[dexter]

@Greg_Deckler I tried as you said below, it is not working. Explaining again for more clarity :

I have Table1, Table2 and when user click on  any column in Table1,i am showing more detailed information of the selected row in Table2 which user has clicked on. So when user again click on any column/data in Table2, i don't want Table2 or Table1 to change the data accordingly. I just want Table2 to show/change the data when user clicks  on any value in Table1. Hope this functionality is available in Power BI. It is like onclick functionality, when user click on the link we show more detailed information about that...I would like to show the columns as a hyperlink so that user know that it has more information, but as my columns are the calculated measures i read that we cannot give hyperlinks to measures so i'm trying this approach as an alternative..

[Greg_Deckler]

Well, I wish I could post my video of me doing exactly what you describe. But in lieu of that, see attached PBIX and go to page 2. Table 1 filters Table 2. Table 2 does not filter Table 1.

[dexter]

@Greg_Deckler , Yes i want the same functionality, unfortunately i could not open the .pbix file you shared as it is asking to Download the latest version of PowerBI which i cannot do now..I am using PowerBI (march2018) version.

[Greg_Deckler]

I did a Publish to Web:

https://app.powerbi.com/view?r=eyJrIjoiODg1OWJlZTYtZmU3ZC00N2U1LWE0ZmEtMGQ1MmM3MmZkNWJiIiwidCI6IjRhM...

Page 2

[dexter]

Thanks for publishing on the web. Two things i noticed was
1)When user click on Table1, Table2 is showing more detailed information which is required but again when user
is clicking on any row on Table2 it is showing all the data in Table2, i was looking only when user click on Table1 based on the
selection Table2 data is changed and even when user click on any data in Table2 it should not change Table2 or Table1.

2)When user click on row with Pname as Mobile and A1 count value which is 2, i want to show detailed information of Mobile for level A1 in Table2 instead of showing all the levels information for Mobile in Table2. Similarly when user clicks on first row Laptop on A2 count value, Table2 should show Laptop information for level A2. (Please see the screenshot attached in the question raised above).
Here A1 Count,A2 Count,A3 Count are the measures created to count A1,A2 and A3.

Sample measure for A1 Count:
A1 Count =
CALCULATE(
COUNTA('ProdData'[Level]),
'ProdData'[Level] IN { "A1" }
)

Hope i'm clear now.Aprreciate all your inputs.

[Greg_Deckler]

OK, I re-published such that it should fix 2 I think. I still don't understand 1 below.

When user is clicking on any row on Table 2 it is showing all data in Table 2. But you didn't want Table 2 to change no matter what the user does so that's a good thing?

I still can't figure out your requirements.

[dexter]

@Greg_Deckler , Sorry if i'm confusing..I am seeing the same display at it was earlier.
In Table1 can you please show the A1 Count,A2 Count,A3 Count measures along with Pname and when user click on
A2 Count of Laptop row, i want to display detail information for product Laptop for only level A2 in Table2.
(Please see the image attached at the question raised above where i'm showing Pname,A1 Count,A2 Count,A3 Count in Table1).

Below are the measures which i have added in my table and shown in Table1.

A1 Count = 
CALCULATE(
COUNTAX(
FILTER ( 'ProdData', 'ProdData'[Level] = "A1"),
'ProdData'[Level] 
))

A2 Count = 
CALCULATE(
COUNTAX(
FILTER ( 'ProdData', 'ProdData'[Level] = "A2"),
'ProdData'[Level] 
))

A3 Count = 
CALCULATE(
COUNTAX(
FILTER ( 'ProdData', 'ProdData'[Level] = "A3"),
'ProdData'[Level] 
))

For point1, i was saying when Table2 is showing some filtered information and again if user click on any value in Table2, it is reloading the Table2 with all data.
I want Table2 to only change/show detailed data when user click on Table1.

[Greg_Deckler]

OK, try again as Publish to Web caches stuff. In fact, wait about 30 minutes or so because I just re-published now that I understand and fixed 1. Basically, I was using a Column Chart visualization to overlay the 2nd table and I switched this to a Card visualization. This fixes the issue because clicking on the Card visualization does not reset Table 2 like the Column Chart visualization was doing. So, I was incorrect when I said any visualization should do. Nope. I also changed my measure that I put in the card visualization to Measure 6 = " " and turned off the title, category display, etc.

I'll see about trying out the solution with what you have for your measures.

[FilipHanus]

Hi, can you sent me pbix file with this solotion? 

Thanks

[PattemManohar]

@dexter Did you tried my solution ?

[dexter]

@PattemManohar, Sorry for late reply.Thanks for the inputs.Duplicating the table may reduce the performance. My actual table contains large data , almost 50 columns and laks of rows. So i think duplicating the table doesn't work in my case.

[v-cherch-msft]

Hi @dexter

It seems you need to drag the 'Level' column to the table as below.

Regards,

Cherie

[PattemManohar]

@dexter Could you please try this...

Steps I've done to achieve this.

1. Duplicate the Products table and called it as ProductsCopy

2. Edit the relationship as below

3. Create Matrix table which is summary from Products table

4. Create Detail table which is detailed level from ProductsCopy table

Now you can see the summary is not filtered even when you click on detailed table...

But detailed will be filtered when you click on the summary table..

Hope this helps !!

[dexter]

@PattemManohar, Thanks for the inputs, I tried as you suggested above. The output it is displaying is same as i have written for the Greg Deckler post . When i click on Table1, Table2 data is showing the filtered information but again when clicked on any row/column in Table2, Table2 data is getting changed and displaying all the informaion. I want Table2 to change/show filtered data when user click on any data in Table1. And as mentioned for Greg Deckler post, when user click on A1 count for Laptop, i want to show only level A1 detail information in Table2. Another concern is my actual table has lot of data around (50k rows and 30 columns..) .When i duplicate the table will it effect the performance?(After every 30mins we are refreshing our graphs so at that time i think it effects the performance...)