cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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

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?

 

Relationship between tables.jpg

 
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

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.

View solution in original post

4 REPLIES 4
Highlighted
Super User IV
Super User IV

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


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted

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?

Highlighted

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?

 

3.jpg4.jpg

 

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,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted

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.

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors