Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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!!
Solved! Go to Solution.
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.
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
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
112 | |
94 | |
91 | |
82 | |
69 |
User | Count |
---|---|
161 | |
127 | |
124 | |
108 | |
97 |