Forum Discussion
Helper IIIShow All Related Records Between Tables when One Table is Filtered to Show Most Recent Records
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
3 Replies
- Greg_Deckler
Community Champion
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
V-lianl-msftCommunity Support
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.
MTOnetV-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?
