How to setup a visual for a table with only a selected record from another visual data

[kentchiu]

Hi,

I need some help setting up a visual that only shows the record/data referenced in a different Visual on the same page.

Table 1 (Versions):  RP Ticket, Release name, DAY, ...

Table 2 (IssueFixVersion): Release Name, Issue Key, ...

I have 2 visuals on the same page:

1. Visual#1 for a table that lists all the Version records based on a filter.

2. Visual#2  for a table that lists all the issues related to the Versions.

I want to get Visual #2, which lists all the issues based on the records in Visual#1.  Currently, it works if I click on the record in Visual#1, and Visual#2 lists all the records based on the related Release Name. So far so good.  However, If no selection is made in Visual#1 (Click on the white space in Visual#1), Visual#2 lists all the issues instead of only the issues related to all the records listed in Visual#1.

In other words, if no record/Release Name is selected, Visual#2 should only list all the issues related to all listed releases in Visual#1.

Current Behavior:

If a release is selected, the Visual#2 works correctly:

Thank you all for your help.

--Kent

[v-linyulu-msft · Accepted solution]

Thanks for the reply from amustafa , please allow me to provide another insight:

Hi, @kentchiu 

Regarding the issue you raised, my solution is as follows:

1.Firstly, I created the following sample data and established a relationship between the two tables:

This is the current page, with the upper section referred to as visual1 and the lower section as visual2.

2.Secondly, I created the following measures:

MEASURE = 
IF (
    MAX ( 'visual2'[RElEASE NAME] ) IN VALUES ( 'VISUAL'[RElEASE NAME] ),
    1,
    0
)

3.Next, I applied the measures to visual2:

4.Finally, here are the results, which I hope will meet your needs:

This is the outcome when visual1 has not been selected:

This shows the result when one row from visual1 is selected:

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[kentchiu]

Hi Amustafa, thank you for your reply and suggestion. I did try that. It seems to work if I set a slicer with only one selection in visual#1.  That approach prevents the listing of all releases. It does not work as I expected.  The goal is to list all the issues related to the release selection, individually or all of the releases.

[v-linyulu-msft · Accepted solution]

Thanks for the reply from amustafa , please allow me to provide another insight:

Hi, @kentchiu 

Regarding the issue you raised, my solution is as follows:

1.Firstly, I created the following sample data and established a relationship between the two tables:

This is the current page, with the upper section referred to as visual1 and the lower section as visual2.

2.Secondly, I created the following measures:

MEASURE = 
IF (
    MAX ( 'visual2'[RElEASE NAME] ) IN VALUES ( 'VISUAL'[RElEASE NAME] ),
    1,
    0
)

3.Next, I applied the measures to visual2:

4.Finally, here are the results, which I hope will meet your needs:

This is the outcome when visual1 has not been selected:

This shows the result when one row from visual1 is selected:

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[kentchiu]

Hi Leroy,

Thank you for helping solve the issue. Your suggestion for the solution seems working as I wanted. 

Could you please explain the solution? I'd like to know more about the measure and how it works.

Thank you so much.

-Kent 

[v-linyulu-msft · Accepted solution]

Hi, @kentchiu 
 

Thank you for your prompt response and for accepting my answer as a solution. I appreciate the recognition.

Here’s a further explanation of my solution:

1.Firstly, our approach primarily revolves around the application of measures and filters. In the measures, we first utilise the VALUES() function to extract the 'RELEASE NAME' column from your visualisation as a virtual table (which you might also consider as a virtual column).

VALUES function (DAX) - DAX | Microsoft Learn

2.Secondly, we employ the MAX() function to retrieve the current row value, allowing us to compare the 'RELEASE NAME' column in Visual 2 with that in Visual 1, thereby returning either 1 or 0.

MAX function (DAX) - DAX | Microsoft Learn

3.Finally, we achieve this using a visual-level filter.

Add a filter to a report in Power BI - Power BI | Microsoft Learn

4.For more details, please refer to:

Tutorial: Create your own measures in Power BI Desktop - Power BI | Microsoft Learn

https://learn.microsoft.com/en-us/dax/dax-operator-reference#logical-operators

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[kentchiu]

Hi Leroy,

Thank you again for the explanation.  I learned something new about using the measure to select the records. I appreciate your effort.

Regards,

Kent

[amustafa]

Hi Kent, perhaps you can make the Visual#1 as a Slicer visual and set it to select only one value.