Assistance with PQE and visuals

[nprevallet]

Hello everyone, and thanks for your time. I’ll try to break this down as clearly as possible.

I’m working with a Salesforce object that contains product information, as well as two Excel spreadsheets that contain additional information for some of the same product codes (but not all of them). My goal is to use the Product Code table from Salesforce and bring in the data from the two external spreadsheets so I can combine information from all three sources into a single visual.

I’ve already cleaned up the Product Code query by removing unnecessary columns, renaming the columns I’m keeping, and eliminating duplicate product codes. Then I created an index column and named it ProducIndex. I also inserted an additional row into the Product Code query to prevent my visuals from breaking. This row represents an “unknown” entry: both the product name and product code are set to “unknown,” and the ProductIndex is set to 0. I appended this row to the Product Code table.

Next, I merged the two Excel data sources—each of which contains product codes—with the Product Code query and pulled the ProductIndex into each of those data sources. Lastly, in the Relationships view, I created relationships between the two Excel sources and the Product Code table using ProductIndex. my relationships are Many:1 and all flow in a single direction.

I expected this setup to allow me to pull data from all three sources into a single visual, but when I try to do that, I get an error asking me to fix the relationships. At this point, I’m not sure exactly what I’m doing wrong, and I would really appreciate some guidance. I’m still fairly new to Power BI, but this project is business‑critical.

Thanks again.

[nprevallet · Accepted solution]

I resolved this by adding a dax measure to both of my fact tables and now it works splendidly. Feel free to mark this resolved.

[MFelix]

Hi @nprevallet ,

Glad you were able to figure it out and apologies for not getting back sooner.

[MFelix]

Hi @nprevallet ,

Based on your description everything is setup correctly the only question is what fields are you bringing from each table.

The dimension table (the one side of the relationship) must be used for the information that is common to all table that will allow you to get information from the different facts however this may depend on the type of data because if you are doing a sum it should work properly, however if you are getting the detailed information of a text field, getting all values (on the many side) for each table then you will get an error because the details will not have a match.

Can you please explain a little bit better what is the expected result and what fields you are using.

[nprevallet]

My product code table includes the code, description, division, franchise, business unit, and legal manufacturer. Additionally, the two Excel sheets that I linked to the product code table via the ProductIndex will bring in deliverable dates related to the product codes. I also want to bring in a risk classification for each product code.

So in summary:

• I have one product code table containing the core product details.
• Two additional sheets provide deliverable dates for specific product codes.
• One of those sheets also includes the risk classification for certain codes.

I hope this clarifies things rather than making them more confusing.

[MFelix]

Hi @nprevallet ,

In this case you have several dates for each product code and also several risk classifications is that it? And you want to bring each line of the two tables into a single visual.

[nprevallet]

Yes, exactly. Each spreadsheet corresponds to a project which contains product codes and a deliverable date. One of the sheets also has a risk classification which corresponds to a product code. I want to bring the deliverable dates and risk classification into 1 visual, and that visual also will contain details of the product codes being pulled from salesforce.