topic Re: Slicer to Operate 2 tables in DAX Commands and Tips

Slicer to Operate 2 tables

GREGBLACK — Tue, 04 Mar 2025 09:12:29 GMT

Good morning folks...i have a possibly interesting issue that i know should be an easy fix but soemhow it is just not working.
I have several tables that i am creating my report off. I need to insert a slicer that will operate across 2 of these tables--one which is d365 data and one which is navision data.
i created a bridge table to connect some tables based on account owner--who can have account that have d365 and navision data.
nav data is on the sps table. d365 data is on the d365 table.
the bridge is built on the one side on the bridge table using account owner to the many side [nav table] using account owner
there is also a 1 to 1 relationship between the bridge table and another table called acccount owner
i have a dax calc created where i have identified some account owners that will roll up to specific segments and where i have not identified an account owner i wnat the VAR Default segment to be the segment of the account in the nav table
just to say in the nav table i have account owners who can have several accounts which can have different segments but each accoutn can only have one unique segment
the dax looks like this:

Teams =

VAR SellerfromTrenderTable = 'Bridge_Acc_Owner'[A/c Owner]

VAR DefaultSegment =

CALCULATE(

SELECTEDVALUE('NavTable''[segment]),

FILTER(

'NavTable',

'NavTable'[Account_owner] = SellerfromTrenderTable

)

RETURN

SWITCH(

TRUE(),

SellerfromTrenderTable IN {"1","2", "3", },"DACH Local Govt",

SellerfromTrenderTable IN {"4","5","6", } ,"DACH Federal Govt",
DefaultSegment

I keep getting blanks for the defaultsegment?

this is the data model
product table on the one side to d365 table on the many side related by product [active]
nav tabl on the many side to bridge table on the one side related by account owner [active]
bridge table on the one side to account owner on the one side related using account owner [active]
nav table on the one side to account table on the many side related using id [[active]
account table on the one side to d365 table on the many side using account [active]
account owner table on the one side to account table on the many side using account owner [deactivated as i buuilt the bridge table]
account owner on the one side to d365 table on the many side using account owner [ not active ]

i believe the VAR Defaultsegment should be a simple fix as there is an active relationship between the nav table and the bridge table but it doe not seem to want to work
am i mover complicating it????

Re: Slicer to Operate 2 tables

bhanu_gautam — Tue, 04 Mar 2025 09:35:15 GMT

GREGBLACK Ensure that the relationships between your tables are correctly set up and active. Specifically, verify that the relationship between the NavTable and the Bridge_Acc_Owner table is active and correctly defined.

Make sure that the Account_owner values in the NavTable match the A/c Owner values in the Bridge_Acc_Owner table. Any mismatch or missing values could result in blanks.

To debug the DAX formula, you can create a simple measure to check the intermediate results.

DAX
SellerfromTrenderTableValue = 'Bridge_Acc_Owner'[A/c Owner]
DefaultSegmentValue =
CALCULATE(
SELECTEDVALUE('NavTable'[segment]),
FILTER(
'NavTable',
'NavTable'[Account_owner] = 'Bridge_Acc_Owner'[A/c Owner]
)
)

Use LOOKUPVALUE: Instead of CALCULATE and FILTER, you can use LOOKUPVALUE to directly fetch the segment value

DAX
Teams =
VAR SellerfromTrenderTable = 'Bridge_Acc_Owner'[A/c Owner]
VAR DefaultSegment =
LOOKUPVALUE('NavTable'[segment], 'NavTable'[Account_owner], SellerfromTrenderTable)
RETURN
SWITCH(
TRUE(),
SellerfromTrenderTable IN {"1","2", "3"},"DACH Local Govt",
SellerfromTrenderTable IN {"4","5","6"} ,"DACH Federal Govt",
DefaultSegment
)

Re: Slicer to Operate 2 tables

GREGBLACK — Tue, 04 Mar 2025 11:21:59 GMT

thnaks for your reply--I ma more used to Tableau and this type of calc is quite easy enough in Tableua when i have the established relationship.
your debug DAX--what is this trying to achieve--is it to see if the account onwer exists in both the bridge table and the nav table?

Re: Slicer to Operate 2 tables

GREGBLACK — Tue, 04 Mar 2025 13:15:02 GMT

all i really need ot do is to assign the segment at the navid or account level in the nav table when i don;t find a match based on the VAR SellerfromTrendertable list.
these unmatached names could have several accounts which will have differtn segments--so I need ot bring in the navid of the accoutn from the nav table as well inot the VAR DefaultSegment calc so that the DAX knows to look at a more granular level as opposed to just the account owner level?

Re: Slicer to Operate 2 tables

GREGBLACK — Tue, 04 Mar 2025 16:01:57 GMT

hi--i have played around with the dax and now i am getting blanks where an account owner has accoutns in several segments--each accutn has a unique navid and segment but the dax can't seem to associate the segment to the navid
this is my revised dax:

Teams =

VAR SellerfromTrenderTable =

LOOKUPVALUE(

Trender[A/c Owner],

Bridge_Acc_Owner[A/c Owner]

)

-- Retrieve Account Owner from Bridge Table

VAR AccountOwner =

LOOKUPVALUE(

NAV TABLE[Account_owner],

Bridge_Acc_Owner[A/c Owner]

)

-- Get the segment for each NAVID ensuring no blank values

VAR DefaultSegment =

CALCULATE(

SELECTEDVALUE('NAV TABLE'[Segment]),

'NAV TABLE'[Account_owner] = AccountOwner

)

RETURN

SWITCH(
TRUE(),
SellerfromTrenderTable IN {"1","2", "3"},"DACH Local Govt",
SellerfromTrenderTable IN {"4","5","6"} ,"DACH Federal Govt",
NOT(ISBLANK(DEFAULTSEGMENT)),DEFAULTSEGMENT)

How can i make sur that even if an account oner has several accounts spread across multiple segments that the dax returns the segment at the navid level

Re: Slicer to Operate 2 tables

GREGBLACK — Wed, 05 Mar 2025 08:38:09 GMT

Hi all--any and all help would be greatly appreciated here--thanks

Re: Slicer to Operate 2 tables

v-aatheeque — Sun, 04 May 2025 17:02:05 GMT

Hi GREGBLACK ,

Modify the DAX with reference to the specific NAVID when calculating the segment.

below is the revised DAX measure :

Teams = VAR SellerfromTrenderTable = LOOKUPVALUE( Trender[A/c Owner], Trender[A/c Owner], Bridge_Acc_Owner[A/c Owner] ) -- Retrieve NAVID for the current row VAR CurrentNAVID = SELECTEDVALUE('NAV TABLE'[NAVID]) -- Get segment at NAVID level to ensure uniqueness VAR DefaultSegment = CALCULATE( SELECTEDVALUE('NAV TABLE'[Segment]), 'NAV TABLE'[NAVID] = CurrentNAVID ) RETURN SWITCH( TRUE(), SellerfromTrenderTable IN {"1", "2", "3"}, "DACH Local Govt", SellerfromTrenderTable IN {"4", "5", "6"}, "DACH Federal Govt", NOT ISBLANK(DefaultSegment), DefaultSegment )

Hope this helps !

If this post was helpful, please consider marking Accept as solution to assist other members in finding it more easily.

If you continue to face issues, feel free to reach out to us for further assistance!

Re: Slicer to Operate 2 tables

v-aatheeque — Thu, 08 May 2025 11:19:32 GMT

Hi GREGBLACK ,

We haven’t heard back from you regarding our previous response and wanted to check if your issue has been resolved.

If it has, please consider clicking “Accept Answer” and “Yes” if you found the response helpful.
If you still have any questions or need further assistance, feel free to let us know — we're happy to help!

Thank you!

Re: Slicer to Operate 2 tables

v-aatheeque — Sat, 07 Jun 2025 15:42:21 GMT

Hi GREGBLACK ,

Has your issue been resolved? If a community member's response addressed your query, please consider marking it as Accepted Answer and click Yes if you found it helpful.

If you have any further questions, feel free to reach out.
Thank you for being a valued member of the Microsoft Fabric Community Forum!

Re: Slicer to Operate 2 tables

v-aatheeque — Sat, 07 Jun 2025 15:44:24 GMT

Hi GREGBLACK ,

If the response helped resolve your issue, it would be greatly appreciated if you could mark it as the Accepted Answer — doing so helps others in the community who may be facing a similar challenge.

If you still have questions or need further assistance, feel free to share more details — we’re always here to support you.

Thanks again for being an active part of the Microsoft Fabric Community!