Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers!
Enter the sweepstakes now!Preparing for a certification exam? Ask exam experts all your questions on May 15th. Register now.
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.
Check out the April 2025 Power BI update to learn about new features.
Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
User | Count |
---|---|
77 | |
76 | |
69 | |
48 | |
40 |
User | Count |
---|---|
62 | |
41 | |
33 | |
30 | |
30 |