Data Model Design

Power BI Data Model
1

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.

  1. If the Demographic field (Institution PIN) contains data, I want to connect to the demographic file.
  2. If the Demographic field (Institution PIN) does not contain data, I want to keep the customer master data (address, etc.).
  3. 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.

Sample Queries
Sample Queries794×209 6.47 KB

2

Hi @npeterson. Thanks for the question. You seem to be off to a good start. I think you model plan sounds good at first glance, and there shouldn’t be issues creating an “address” measure that uses the institution address if it exists, otherwise the customer address. I’d go ahead and start a preliminary PBIX and the forum can revisit once a PBIX and desired outcome mock-up are available (you can create a small sample dataset if need be).
Greg

1 Like
3

Hi @npeterson, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.

  • When posting a topic with formula make sure that it is correctly formatted to preformated text </>.

  • Use the proper category that best describes your topic

  • Provide as much context to a question as possible.

  • Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline https://forum.enterprisedna.co/t/how-to-use-the-enterprise-dna-support-forum/3951. Not adhering to it may sometimes cause delay in getting an answer.