Direct Query Table Relationship Not Functioning As Expected

[Anonymous]

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.

[v-xicai · Accepted solution]

Hi @Anonymous ,

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.