Table visual to show all rows of related table exc...

TasCat09 · ‎08-29-2025

I have 2 three tables that are joined BU[BUID] 1<->* BUPR[BUID] , BUPR[PRID]*<->1 Process[PRID] (because the relationship between BU and Process is many to many). I want to create a dashboard that has 2 table visuals and 1 slicer. The user can select a BU[Name] from the slicer and I want table 1 to display all Process that are related to the selected BU. I want the second table to show Process[Name] and BU[Name] for every other BU not selected in the slicer.
So far I can only solve the first table if I make the relationships between the 3 tables bi-directional (which I know is not best practice) and cannot solve the second table despite trying several different dax measures.

The second part of my problem seems similar to the solution provided in ‘Filter a table visual based on a slicer selection’ but this solution doesn’t have to traverse three different table.

It seems like it should be possible but I have tried a lot of different ways and I can’t figure it out. Any help would be appreciated.

Here are some example tables I used to test several unsuccessful attempts.
BU:

BUID	Name
1	BU1
2	BU2
3	BU3
4	BU4

BUPR:

BUID	PRID
1	2
2	2
2	3
3	4
4	1
4	3

Process:

PRID	Name
1	Process1
2	Process2
3	Process3
4	Process4

Here is what I would like the result to be:

TasCat09

Thank you @v-aatheeque
Your explanation makes sense and the solution you provided using FIRSTNONBLANK does solve the problem where nothing is returned when there are multiple processes that could be returned for a particular BU. However, the solution has created a new problem where only one Process is returned in table 1 should when more than 1 should be returned (e.g. Selecting BU2 should result in table 1 returning Process2 and Process3)

v-aatheeque

Hi @TasCat09 ,

You can modify the measure to achieve the following result:

ProcessNames =

CALCULATE(

    CONCATENATEX(

        VALUES(Process[Name]),

        Process[Name],

        ", "

    ),

    CROSSFILTER(Process[PRID], BUPR[PRID], Both),

    BU[Name] = SELECTEDVALUE('BU Selected'[Name])

)

Expected result :

Attached file for reference.

Hope this helps !!

v-aatheeque

Hi @TasCat09

Just checking in to see if you had a chance to review the previous response. If you still need assistance, please let us know and we’ll be glad to help.

TasCat09 · ‎08-31-2025

Thank you @FBergamaschi
The solution you have provided is close to the behaviour I am looking for but not quite. I found 2 issues when I tried to implement your solution in my PBIX file, I can sort of explain 1 but number 2 I cannot:
1. The first table responds to the slicer and shows related processes only where there i only 1 row in the BUPR table. Is this because the ALLNOBLANKROWS function only returns distinct rows and there are no distinct rows when 2 or more processes are related to a BU[Name]? I found table 1 showed the correct Process[Name] for BU 1 and 3 but returned nothing for BU 2 and 4.
2. The second issue is probably related to my version of PowerBI (I am using Version: 2.138.2255.0 64-bit (January 2025)) because I can verify the solution in the PBIX you provided does produce the correct outcome for the second table (i.e. any BU selected in the slicer and its related processes are excluded) but I can't replicate it in my PBIX file?

v-aatheeque

Hi TasCat09,

Thanks for the update. Thank(Super user for sharing file)

After reviewing the pbix, the reason you are getting blank for BU2 and BU4 is because of ALLNOBLANKROW

The ALLNOBLANKROW function only filters the blank row that a parent table, in a relationship, will show when there are one or more rows in the child table that have non-matching values to the parent column.

After modifying the measure, you get the following result-