I’m working on a new model and would like some assistance in setting this up. We are an education company, selling products and services to districts and schools. We lease data from an organization that includes demographic details for the universe of educational institutions in the United States. As part of the regular data maintenance, the unique identifier from this demographic file is appended to our customer file.
In addition to selling to these institutions, we also sell products direct to consumer. In those instances, there is no unique identifier from our demographic file.
Our transactional data from the ERP includes both a bill-to customer number and a ship-to customer number. In some cases, a district is the bill-to and a school is the ship-to. In other cases, the school is the bill-to. Because of this, a single institution can have more than one customer number. But they would have the same unique demographic identification number.
I’d like to create a report that allows users to view either bill-to transactions or ship-to transactions.
My challenge is in creating a modified customer file.
- If the Demographic field (Institution PIN) contains data, I want to connect to the demographic file.
- If the Demographic field (Institution PIN) does not contain data, I want to keep the customer master data (address, etc.).
- In both situations, I need to keep the customer number as the link to the transactional data.
I’ve not yet built the model, so I don’t have a PBIX file to share. Please help me brainstorm possible solutions to this data problem. Below are some examples of the fields in the 3 tables.