Create a relationship between two tables with a calculated column based on the same table

[dickvankl]

Hello,

I have the following issue. I have a table with all subscriptions (Abonnementen) and a table with all online use (Epaper gebruik dagb.). In both tables a relationship number is present, so a many to many relation is possible, because one relation can have as well multiple subscriptions as multiple times of online usage.

I created a calculated column that (based on start and end date of the subscription in the subscription table) indicates which subscription number the online usage is related to. Now I would like to create a relationship on subcription number (to have a 1 to many relationship), but I can't delete the relation on relationship number, because the calculated column subscription number in the online usage table depends on the relationship on relationship number. Does anyone know how I can solve this?

[Greg_Deckler]

Would need to see some sample data to understand what you are doing here. But, as you are probably aware, Many 2 Many = bad

[dickvankl]

Hello,

Hereby a subset of the data in screenshots (is it possible to share documents?)

the online usage data:

And the subscription data:

As can be seen in the previous message I related them by the variable "relatienummer dagblad"which means "relationshipnumber newspaper".

I used this calculated column to get the right subcriptionumber ("abonnementsnummer") at the online usage dataset:

Abonnementnummer =

CALCULATE (

    FIRSTNONBLANK( Abonnementen[Abonnementnummer]; 1 );

    FILTER (

        Abonnementen;

        'Epaper gebruik dagblad'[Relatienummer dagblad] = Abonnementen[Relatienummer Dagblad]

            && Abonnementen[Startdatum] <= 'Epaper gebruik dagblad'[datum]

            && OR(Abonnementen[Stopdatum] >= 'Epaper gebruik dagblad'[datum];ISBLANK(Abonnementen[Stopdatum])

    )

))

Now I want to relate the tables by this subscription number ("Abonnementsnummer") calculated column, however this column depends on the relationship between the online usage and subscription data on the variable "relatienummer dagblad". So this relationship cannot be deleted and thereby i cannot create an active relationship between the tables with the variable subscriptionnumber( "abonnementsnummer"), because there is already on on "relatienummer dagblad".

Do you understand what I mean?

[v-lid-msft]

Hi @dickvankl ,

Sorry for that, but we cannot understand "this column depends on the relationship ...So this relationship cannot be deleted" , we applied your forumla on the sample you shared and create relationship based on the "Abonnementsnummer" column, or you want to keep two active relationships?

If it doesn't meet your requirement, kindly share your sample data and expected result to me if you don't have any Confidential Information. Please upload your files to OneDrive For Business and share the link here.

By the way, PBIX file as attached.

Best regards,

[dickvankl · Accepted solution]

Sorry, I made a mistake. I thought that the calculated column subscription number depended on the relationship on relationship number between the twp tables, but that was not the case. I could just create a relationship on subscription number.

Thank you for your help and sorry for the mistake.