Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
78 | |
65 | |
53 |
User | Count |
---|---|
144 | |
103 | |
98 | |
85 | |
64 |