Is it possible to seperate a table into two tables in PowerBI?

Question

In PowerBI I am currently bringing in a query which essentially has two mergekeys and values.

It has a "development" column which is associated with a "month+year" column. However, it also has a seasonality column which is associated with a "month" column.

I wish to apply the development column in a calculation and have it only impact the "month+year", but also apply the seasonality where it will apply to every single row.

I have created a relationship on the "month+year" column, which allows me to interact with the table. However, I cannot apply seasonality to anything but the "month+year"s.

I attempted to use LOOKUPVALUE (in an attempt to do my multiplication) but it said that the relationship didn't exist.

I think the correct approach is to either change my data creation process so that they are input as separate tables or to separate them within PowerBI.

Is it possible to separate a table

a b c d
5 3 1 3
2 3 6 5

into

a b
5 3
2 3

and

c d
1 3
6 5

?

Edit:

It seems like the correct approach is to use USERELATIONSHIP, however when I tried this

T =
CALCULATE (
    DIVIDE (
        Tra_GLM_EXPOSURE_1809[ADFrequency],
        Commercial_Development[Taxi_AD Net]
    ),
    USERELATIONSHIP ( Commercial_Development[MonthName], Tra_GLM_EXPOSURE_1809[MonthName] )
)

It is trying to return a single value, rather than a row-wise operation?

Edit 2:

I used the guide Alexis' answer to duplicate the dataset and then create a relationship with the duplicate.

It's still not very clear what tables and relationships you have in your data model and what exactly your calculated column is intended to do. — Alexis Olson

Alexis Olson Alexis Olson · Accepted Answer · 2019-04-15T15:56:36

You can certainly split a table either in DAX or in the query editor using SELECTCOLUMNS or Table.SelectColumns, respectively, but I don't think that's your best option.

You can have multiple relationships between tables (though only one active relationship) and choose which one to use in a measure with the USERELATIONSHIP function.

Here's an example:

POWER BI AND DAX – USERELATIONSHIP TO WORK WITH MULTIPLE DATES.

Is it possible to seperate a table into two tables in PowerBI?

1 Answers