many to many relationship between dimensions

[MyraB]

I have two dimensions customer and Account that are related to each other using a factlessfact (CustomerAccount),  The two dimensions are related to other facts (Sales..etc).

I want to be able to show all values from Account dimension related to a customer. Currently it only shows the accounts related to the customer only if they have a transaction in Sales.

I want to avoid using bi-direction. What is the best way to be able to show all accounts related to a customer irrespective of transaction in sales

[speedramps · Accepted solution]

Novices often think they "must" import excatly the same data model as the data source.

Whereas it is best pratice to data model your data in the queries to a star schema.

Try merge Custommer, Customr account and acount in to one table.

Then have a 1 : M relationship to Sales

This dax measure

HasSales = INT(NOT(ISEMPTY(Sales)))
will return 1 if your context has any sales.

Eg Region, Customer, Salesrep or Account  

please clcik thumbs up and accept as solution buttons. thanks