N TO M JOIN

Hi everyone,

I have the following issue which is driving me mad:

I have two tables which I like to join TRANSACTION and COMPETING_PRICES. In the TRANSACTION table I have information per transaction like ID, DATE, DEALER, PRICE, etc. and in the table COMPETING_PRICES I have TRANSACTION_ID (foreign key to ID), DEALER, PRICE.

Now I would like to get a table with the following columns:

- DEALER
- amount of IDs in TRANSACTION per DEALER
- amount of TRANSACTION_IDs in COMPETING_PRICES per DEALER

So I created a query for TRANSACTION and COMPETING_PRICES and a query which a unqiue List of the field DEALER. This works totally fine; I can use the unique DEALER variable and create measures with DISTINCTCOUNT on ID and TRANSACTION_ID. Now comes the issue: When I apply a filter (e.g. DATE > x) on TRANSACTION it will change the amount of IDs TRANSACTION but the measure which is counting the distinct TRANSACTION_IDs is not changing and therefore not displaying me the correct values. I somehow need to filter the TRANSACTION_IDs in COMPETING_PRICES depending on the filters in TRANSACTION, but I don't know how. Power BI won't let me join TRANSACTION and COMPETING_PRICES via a query which contains unique IDs.