Slicer to Operate 2 tables

[GREGBLACK]

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

[GREGBLACK]

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

[GREGBLACK]

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?

[GREGBLACK]

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?

[bhanu_gautam]

@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
)

[GREGBLACK]

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],

        Trender[A/c Owner],

        Bridge_Acc_Owner[A/c Owner]

    )

-- Retrieve Account Owner from Bridge Table

VAR AccountOwner =

    LOOKUPVALUE(

       NAV TABLE[Account_owner],

        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