Many to Many my only option with one table created by Distinct

[PhilSmith]

I have a sales table, (many duplicates,) and I am trying to create a One to Many relationship with CustomerBrandCatActive.  This table will ultimately be a bridge.  There are no other relationships connected to CustomerBrandCatActive.

CustomerBrandCatActive = DISTINCT(filter(CustomerBrandCat,CustomerBrandCat[Active]<>"N"))

Distinct means no duplicates, and I have verifed there are no duplicates.  Yet, many to many is the only option BI will give me.

What makes it think this is not a table of unique values?  How can I fix this?

Thanx

Phil

[PhilSmith]

Thank you.   This works.  I would love to know why it works though.  The results in the table are are different, by about 40 records.  The results are too big to export, so I can't just compare what is being returned by each option.

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your CustomerBrandCat table looks like, but please try the below.

Or, please share your sample pbix file.

CustomerBrandCatActive =
DISTINCT (
CALCULATETABLE (
SUMMARIZE ( CustomerBrandCat, CustomerBrandCat[Active] ),
FILTER ( CustomerBrandCat, CustomerBrandCat[Active] <> "N" )
)
)