topic Re: Table visual to show all rows of related table except selected in DAX Commands and Tips

Table visual to show all rows of related table except selected

TasCat09 — Sat, 30 Aug 2025 02:35:19 GMT

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:

Re: Table visual to show all rows of related table except selected

FBergamaschi — Sat, 30 Aug 2025 09:06:39 GMT

Done

file is here

https://drive.google.com/drive/folders/1QWH1SxVPf6kmY4frnvA50wUJrG_-oOyF?usp=sharing

A few explanations will follow shortly

If this helped, please consider giving kudos and mark as a solution

me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

Re: Table visual to show all rows of related table except selected

TasCat09 — Mon, 01 Sep 2025 01:09:41 GMT

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?

Re: Table visual to show all rows of related table except selected

v-aatheeque — Thu, 04 Sep 2025 09:18:02 GMT

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-

Re: Table visual to show all rows of related table except selected

TasCat09 — Thu, 04 Sep 2025 20:54:42 GMT

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)

Re: Table visual to show all rows of related table except selected

v-aatheeque — Tue, 09 Sep 2025 06:54:54 GMT

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 !!

Re: Table visual to show all rows of related table except selected

v-aatheeque — Wed, 10 Sep 2025 12:06:53 GMT

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.

Re: Table visual to show all rows of related table except selected

TasCat09 — Fri, 12 Sep 2025 04:15:24 GMT

Thank you v-aatheeque
That is the exact behavior I was hoping for. The only small improvement I made was to replace the ", " sparator in the CONCATENATEX function with UNICHAR(13) & UNICHAR(10) which makes each value appear on a separate row rather than appearing on the same row separated by a comma.