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:
Solved! Go to Solution.
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.
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.