Show All Related Records Between Tables when One Table is Filtered to Show Most Recent Records

[MTOnet]

How would I go about showing all related records between two tables, when one of the tables has a Calcualted Column to determine the most recent occurence of a record?  I am trying to create two Table Visualizations that filter each other when a record is selected in one of them, but not coming up with a goo solution.

Source1

ID	Name	Option	Related Type1	Related Type2	Status	Most Recent
1	Test Name	1	A	B	Status1	0
2	Test Name	2	A		Status1	1
3	Test Name	1	A		Status2	1
4	Dummy Name	1	C	D	Status3	1
5	Placeholder	1	E	F	Status2	0
6	Placeholder	1	E	G	Status3	1
7	New Name	1	H		Status2	0
8	New Name	1	H	B	Status1	1
9	Another Name	1	J	K,L	Status2	1

Source2

ID	Name	Type	Status
A	Test Name	Type1	Status1
B	Some Name	Type2	Status4
C	Dummy Name	Type1	Status5
D	Related Name	Type2	Status1
E	Placeholder	Type1	Status1
F	Another Name	Type2	Status4
G	More Name	Type2	Status6
H	New Name	Type1	Status5
I	Nameless	Type2	Status5
J	Another Name	Type1	Status4
K	Sample Name	Type2	Status5
L	Final Name	Type2	Status6

This data is displyed in table visualizations

Table1 - with visualization level filter Most Recent = 1, as I only need to see one record in the table for each Name/Option pair

Name	Option	Status
Test Name	1	Status2
Test Name	2	Status2
Dummy Name	1	Status3
Placeholder	1	Status3
New Name	1	Status1
Another Name	1	Status2

Table2 - with visualization level filter Type = Type2

ID	Name	Status
B	Some Name	Status4
D	Related Name	Status1
F	Another Name	Status4
G	More Name	Status6
I	Nameless	Status5
K	Sample Name	Status5
L	Final Name	Status6

The interations I am trying to setup

• clicking on Test Name/1 in Table 1, results in1 record (ID B) in Table 2
• clicking on Test Name/2 in Table 1, results in zero records in Table 2
• clicking on Placeholder/1 in Table 1, results in two records (F & G) in Table 2
• clicking on Another Name/1 in Table 1, results in two records (K & L) in Table 2 (with the realization I may need to split the comma seperated values into individual rows)
• clicking on ID B in Table 2, results in two records (Test Name/1 & New Name/1) in Table 1
• clicking on ID F in Table 2, results in one record (Placeholder/1) in Table 1

Creating a Bridge table between the two Sources did not create the results I am looking for.  When doing this, I only get results if the Related Type2 item is on the record that is tagged as being the most recent one.  I'm sure there is a way to do this such that PowerBI does more of the work.  I know I could generate this solution through a series of measures that calculate whether the record should be displayed or not and then updating the visual level filter accordingly.  Doing it in this way creates maintenace issues and also feels like I am forcing the issue while not using Power BI properly and I feel there must be a better solution.

Thank you for your assistance

[V-lianl-msft]

Hi @MTOnet ,

I don't quite understand the logic. I wonder if your description is wrong.

---

@MTOnet wrote:

Source1

ID	Name	Option	Related Type1	Related Type2	Status	Most Recent
1	Test Name	1	A	B	Status1	0
2	Test Name	2	A		Status1	1
3	Test Name	1	A		Status2	1
4	Dummy Name	1	C	D	Status3	1
5	Placeholder	1	E	F	Status2	0
6	Placeholder	1	E	G	Status3	1
7	New Name	1	H		Status2	0
8	New Name	1	H	B	Status1	1
9	Another Name	1	J	K,L	Status2	1

Source2

ID	Name	Type	Status
A	Test Name	Type1	Status1
B	Some Name	Type2	Status4
C	Dummy Name	Type1	Status5
D	Related Name	Type2	Status1
E	Placeholder	Type1	Status1
F	Another Name	Type2	Status4
G	More Name	Type2	Status6
H	New Name	Type1	Status5
I	Nameless	Type2	Status5
J	Another Name	Type1	Status4
K	Sample Name	Type2	Status5
L	Final Name	Type2	Status6

This data is displyed in table visualizations

Table1 - with visualization level filter Most Recent = 1, as I only need to see one record in the table for each Name/Option pair

Name	Option	Status
Test Name	1	Status2
Test Name	2	Status2
Dummy Name	1	Status3
Placeholder	1	Status3
New Name	1	Status1
Another Name	1	Status2

Table2 - with visualization level filter Type = Type2

ID	Name	Status
B	Some Name	Status4
D	Related Name	Status1
F	Another Name	Status4
G	More Name	Status6
I	Nameless	Status5
K	Sample Name	Status5
L	Final Name	Status6

The interations I am trying to setup

• clicking on Test Name/1 in Table 1, results in1 record (ID B) in Table 2
• clicking on Test Name/2 in Table 1, results in zero records in Table 2
• clicking on Placeholder/1 in Table 1, results in two records (F & G) in Table 2
• clicking on Another Name/1 in Table 1, results in two records (K & L) in Table 2 (with the realization I may need to split the comma seperated values into individual rows)
• clicking on ID B in Table 2, results in two records (Test Name/1 & New Name/1) in Table 1
• clicking on ID F in Table 2, results in one record (Placeholder/1) in Table 1

---

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

[MTOnet]

@V-lianl-msft What are you not getting from my description, I'll try and elaborate as I believe I setup the scenario properly.  For the items in red, from Source1, that is the most recent record for that particular Name/Option pair, so that is what should be displayed in the Table visualization.  Then, if I select that record in Table1, Table2 should 1 record, since I want to see all records for that Name/Option pairing, no matter which one the current record is.

@Greg_Deckler Thanks for the link.  Reading what you wrote in that post actually shows I was thinking of doing this in the best way.  I thought maybe I was missing something and there would be a more elegant solution.  Maybe I read over your post before and had that in the back of mind while thinking this through.

As it perstains to the current situation, I'm having difficulty getting the measure to produce the results I want.  I'm using the TREATAS function since I have two unrelated tables and I'm using SUMMARIZE to get only the Relate Type 2 items from Source 1.  I would think I could use this to count how many times a record in Source 2 shows for each Related Type 2.  However, I keep getting a blank table when I select a record in Table 1

CALCULATE(
    COUNTROWS('Source2'),
    TREATAS(
        SUMMARIZE(
            Source1,
            Source1[Related Type 2]),
        'Source2'[Id]))

Any suggestions on how I can get a value to return to my measure on Table 2 so that I can then use a Table level visual?

[Greg_Deckler]

@MTOnet - It sounds like you need to create a Complex Selector, here is an example - https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Complex-Selector/m-p/1116633#M534