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

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,