While using DAX, a cross join occurred when trying to get the detail columns from the dimension

[kemalfaisal]

I have a problem creating a measure in DAX. I have SalesByBA as a fact table and Master_BusinessArea as a dimension table as shown below. These two tables are related in a one-to-many relationship on the businessareacode column.

I would like to calculate revenue for each region code in the fact table. It works perfectly until I add the businessareadescription from Master_BusinessArea to the visualization. All the businessareas of Master_BusinessArea appear like a cross join. I use the following DAX syntax : 

The goal is to display only the businessareadescription for businessareacode as follows:

[amitchandak]

@kemalfaisal , Hope you have only one business area description, there is nothing in Sales BY BA.

It is not possible when use it from Master table

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[kemalfaisal]

Sorry, I was wrong about both tables being joined by the BusinessAreaCode column instead of the CompanyRegionBACode column. So it`s not possible to get BusinessAreaDescription from Master table although I already connected those 2 tables with CompanyRegionBACode?

I have already attached a sample .pbix file

Sales by Region.pbix

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[kemalfaisal]

Hello Ashish
It works pretty well! I didn't expect the DAX terms to run out of the blank data.
Thank you very much.

[Ashish_Mathur]

You are welcome.