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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.