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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.