Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
adunn
Helper I
Helper I

Direct Query Table Relationship Not Functioning As Expected

I am trying to recreate some data I had been able to extract in Import mode, but instead using Direct Queries.

In this instance, I have two tables containing hopefully unique values. Currently, they are showing up as a One-To-Many relationship in Power BI (I am uncertain if this is the cause of my problem).

Essentially, I want to see if a unique ID is in Table 1 and in Table 2 (it is not guaranteed to be in both). I have filtered both data sources and given them a relationship. However, when writing measures and attempting to filter only to rows where the unique ID exists in both tables, it still gives me every row in Table 1, even ones without a match in Table 2. I have checked both tables and there definitely is not a match.

 

This is my DAX formula:

WO's in Engineering and IMOS = CALCULATE(DISTINCTCOUNT('TRADESOFT - WO'[WoNbr]),'TRADESOFT - WO'[WorkOrdStatusNbr] = 1, 'TRADESOFT - WO'[WorkOrdTypeNbr] = 2, 'TRADESOFT - JOBS'[JobStatusNbr] = 2, ISBLANK('IMOS - PROADMIN'[WoNbr]) = FALSE())
 
'TRADESOFT - WO' is Table 1 and 'IMOS - PROADMIN' is Table 2. They have a One-to-Many relationship WoNbr, and I thought this formula would give me rows where a WoNbr existed in Table 1 and in Table 2. Instead it gives me rows for WoNbrs that are not in Table 2.
1 ACCEPTED SOLUTION
v-xicai
Community Support
Community Support

Hi @adunn ,

 

You may change the formula like DAX below, and make sure the Cross filter direction of relationship between the two tables as Both.

 

WO's in Engineering and IMOS =
CALCULATE (
    DISTINCTCOUNT ( 'TRADESOFT - WO'[WoNbr] ),
    FILTER (
        'TRADESOFT - WO',
        'TRADESOFT - WO'[WorkOrdStatusNbr] = 1
            && 'TRADESOFT - WO'[WorkOrdTypeNbr] = 2
    ),
    FILTER ( 'TRADESOFT - JOBS', 'TRADESOFT - JOBS'[JobStatusNbr] = 2 ),
    FILTER (
        'IMOS - PROADMIN',
        ISBLANK ( 'IMOS - PROADMIN'[WoNbr] ) = FALSE ()
            && 'IMOS - PROADMIN'[WoNbr] = RELATED ( 'TRADESOFT - WO'[WoNbr] )
    )
)

 

Best Regards,

Amy 

 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
v-xicai
Community Support
Community Support

Hi @adunn ,

 

You may change the formula like DAX below, and make sure the Cross filter direction of relationship between the two tables as Both.

 

WO's in Engineering and IMOS =
CALCULATE (
    DISTINCTCOUNT ( 'TRADESOFT - WO'[WoNbr] ),
    FILTER (
        'TRADESOFT - WO',
        'TRADESOFT - WO'[WorkOrdStatusNbr] = 1
            && 'TRADESOFT - WO'[WorkOrdTypeNbr] = 2
    ),
    FILTER ( 'TRADESOFT - JOBS', 'TRADESOFT - JOBS'[JobStatusNbr] = 2 ),
    FILTER (
        'IMOS - PROADMIN',
        ISBLANK ( 'IMOS - PROADMIN'[WoNbr] ) = FALSE ()
            && 'IMOS - PROADMIN'[WoNbr] = RELATED ( 'TRADESOFT - WO'[WoNbr] )
    )
)

 

Best Regards,

Amy 

 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.