Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kemalfaisal
Regular Visitor

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

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.

 

kemalfaisal_1-1685094132155.png

 

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 

kemalfaisal_0-1685094644258.png

 

kemalfaisal_2-1685094194788.png

 

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

kemalfaisal_3-1685094321622.png

 

1 ACCEPTED SOLUTION

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

5 REPLIES 5
amitchandak
Super User
Super User

@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.

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

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.