Forum Discussion

Anon1234's avatar
Anon1234
Regular Visitor
1 year ago

Creating dimension tables vs filtering based on columns within the fact table

I'm new to BI and recently ran into an issue creating a simple line graph where my date period on the x axis was jumbled up. The solution was to put the date periods into a separate dimension table a...
Need Help
johnt75's avatar
johnt75
Icon for Super User rankSuper User
1 year ago

When you don't have a natural column to link tables you can use the COMBINEVALUES function to create one.

Create a new table using 

Dim Business Type =
ADDCOLUMNS (
    SUMMARIZE (
        'Table',
        'Table'[Business Type],
        'Table'[High Risk or Mid Corporate],
        'Table'[Low Risk or Trust],
        'Table'[MA Contract Style]
    ),
    "Business Type Link",
        COMBINEVALUES (
            "|",
            'Table'[Business Type],
            'Table'[High Risk or Mid Corporate],
            'Table'[Low Risk or Trust],
            'Table'[MA Contract Style]
        )
)

Then in your fact table create a new column like

Business Type Link =
COMBINEVALUES (
    "|",
    'Table'[Business Type],
    'Table'[High Risk or Mid Corporate],
    'Table'[Low Risk or Trust],
    'Table'[MA Contract Style]
)

You should then be able to create a one-to-many relationship using this column, and you can use the same process for the other dimension table.