Dimension - Multiple Fact Table Columns Customer Names (BillTo Name, ShipTo Name, EndUser Name)

[ScottBrown]

Structure:

I have a dimension table of Customer Names only.

The Fact Table has a BillToName, ShipToName, and EndUserName - not always the same.

The Fact Table consist of SalesLineItem data, many lines per Sales Order.

There is an Index column in the Dimension linking to the Fact for faster indexing at run time.

Goal:

Show a list of Customer Names only in the Dimension to search all 3 Fact columns.

We do not want to rely on Inactive relationships to make seperate visuals per BillTo, ShipTo, EndUser but drill down through all in one visual.

Scenarios Tried:

1. Active/Inactive relationships (Calc Group) - too cumbersome to use and makes too many visuals vs one
2. Linking a Transaction ID (Sales Order Number) to pivot Custoner Names (BillTo, ShipTo, EndUser_ - Creates a many to many and when names are the same duplicates measure values
3. Concatinate Names - Looks weird on the Dimension

Thoughts?

[Anonymous]

Hi, @ScottBrown 

I wish you all the best. Previously we have provided a solution to help you solve the problem. Since we haven't heard back from you yet, I'd like to confirm if you've successfully resolved this issue or if you need further help?
If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.
If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.
Thank you for your patience and look forward to hearing from you.

Best Regards

Jianpeng Li

[Anonymous · Accepted solution]

Thank you DataNinja777

Hi, @ScottBrown 

Based on your description, I've created the following two tables:

I think you can unpivot BillToName, ShipToName, and EndUserName in Power Query to turn it into a 1D table that Power BI accepts:

Finally, you can establish a connection to your CustomerNames dimension table and filter the corresponding data by the type column.

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DataNinja777]

Hi @ScottBrown ,

Thank you for your detailed question. Ideally, your Fact Table should link to a Customer Master Table with unique customer codes. Most ERP systems generate these codes sequentially, ensuring a clean one-to-many relationship between the Dimension Table and Fact Table. This setup avoids many-to-many relationships, prevents duplication issues, and improves performance. If your ERP system provides such customer codes, I recommend downloading the Customer Master Data and linking your Fact Table accordingly.

However, if you're working with customer names instead of unique IDs due to system limitations or data constraints, there are still practical solutions to achieve your goal of searching across the BillToName, ShipToName, and EndUserName columns in a single visual. One approach is to flatten the customer-related columns into a unified mapping table in Power Query. You can do this by duplicating the Fact Table, keeping only the customer columns and the Index column, and using the UNION function to stack the customer names into one column. For example:

let
    FactTable = Table.SelectColumns(Source, {"Index", "BillToName", "ShipToName", "EndUserName"}),
    FlattenedTable = Table.Combine({FactTable[BillToName], FactTable[ShipToName], FactTable[EndUserName]}),
    DistinctNames = Table.Distinct(FlattenedTable)
in
    DistinctNames

If Power Query transformations aren't feasible, you can achieve a similar result in DAX by creating a measure that concatenates distinct customer names from the three columns. This measure can be used in a search visual to filter all customer-related fields dynamically:

Searchable Customers =
CONCATENATEX(
    DISTINCT(
        UNION(
            VALUES(FactTable[BillToName]),
            VALUES(FactTable[ShipToName]),
            VALUES(FactTable[EndUserName])
        )
    ),
    [Customer Name],
    ", "
)

While using unique customer codes is the best practice, these solutions will help you achieve the desired functionality with customer names if codes are not available. Let me know if you need further assistance!

Best regards,

[ScottBrown]

Thank You for the reply.

I have the names in the Dimension already via PowerQuery.

If 2 of the 3 names are the same for the SalesOrder, the measure value is duplicated due to the many to many relationship created - Join 3 names with Sales Order number and link back to the fact with Sales Order Number.