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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

Relationships on Power BI

Hi all,

 

Currently I cannot get screen shots but can describe the issue...

 

I have 4 different tables/queries. They are:

 

1. General Material (Material Number, Division, Description, Height, Width, etc)

2. Material Sales (Material Number, Sales Organization, Distribution Channel, etc)

3. Material Plant (Material Number, Plant, etc)

4. Material Warehouse (Material, Warehouse, etc)

 

For all the queries, the same Material Number can have multiple entries, as they can belong to multiple Divisions, Sales Organizations, Plants, and Warehouses. I was trying to do a Star Schema, where the General Material table is in center (sorry for lack of better terminology), with other tables relating as "attributes" of the center table. The visualization I'm using is the Table. So I'm adding fields from all 4 views. What I have found is.. if for a Material, e.g. 123456, exists in all 4 tables... it will populate in my Table. If it only exists in General, Sales, and Plant, but not Warehouse, it will no longer populate in my Table visualization. The result desired is for Material 123456 to remain in my visualization, even if there is no entry for it in Material Warehouse. I would just want the value under Material Warehouse to be null or blank. 

 

I am relating the tables via Relationships on the Front End and not creating joins/merges in the Power Query Editor. The reason why I am doing this is because these tables are massive, and I thought it'd be more performance-saving if I created these relationships on the front end. I have tried so many different combinations.. Many-to-One and Many-to-Many cardinality... and Cross Filter Single and Both. Do you know what the issue is? Is the only solution we have, creating the joins on the Query Editor side in order for the value to appear as blank/null if the key does not exist in any one of the tables? 

 

Thanks for reading this and leaving any advice/help!!

1 ACCEPTED SOLUTION
v-xicai
Community Support
Community Support

Hi @Anonymous ,

 

Try to create column like DAX below.

 

Column1= CALCULATE ( FIRSTNONBLANK (Material], 1 ), FILTER ( 'General Material', 'General Material'[Material Number]= 'Material Sales'[Material Number])

,FILTER ( 'General Material', 'General Material'[Material Number]= 'Material Plant'[Material Number])

, FILTER ( 'General Material', 'General Material'[Material Number]= 'Material Warehouse'[Material]))

 

Best Regards,

Amy

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-xicai
Community Support
Community Support

Hi  @Anonymous ,

 

Does that make sense? If so, kindly mark my answer as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

 

Best regards

Amy

v-xicai
Community Support
Community Support

Hi @Anonymous ,

 

Try to create column like DAX below.

 

Column1= CALCULATE ( FIRSTNONBLANK (Material], 1 ), FILTER ( 'General Material', 'General Material'[Material Number]= 'Material Sales'[Material Number])

,FILTER ( 'General Material', 'General Material'[Material Number]= 'Material Plant'[Material Number])

, FILTER ( 'General Material', 'General Material'[Material Number]= 'Material Warehouse'[Material]))

 

Best Regards,

Amy

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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