Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

RELATED function for Many to One relationship doesn't work. How else to create a calculated column?

Hi everyone,

 

I have 2 tables (dps_contact_role and dps_customer) related to each other like this:

ValeriaSa_2-1669380093359.png

I am trying to calculate how many customers are missing customer contact ID (which is called contactidentity_id in dps_contact_role_ table). To be more precise I need to visualise using Pie chart, how many customers HAVE customer contact ID in the system and how many are MISSING it.

 

I am creating calculated columns in the table dps_customer:

 

ValeriaSa_1-1669379825292.png

 

Because relationship are Many to One and I am creating the column in lookup table, RELATED function doesn't work. But I have created similar columns for other pie charts where relationship is reverse (where customer table is the fact table) and it worked perfectly.

 

I am a bit confused now. How else can I create a column in customer table for my purpose if I cannot change relationships and I cannot create a column in another table?

 

Thanks in advance!!

 
 
 
 
1 ACCEPTED SOLUTION
v-yiruan-msft
Community Support
Community Support

Hi @Anonymous ,

You can created a calculated column as below in the table 'dps_customer' to get it, please find the details in the attachment.

Customer Contact =
VAR _ciid =
    CALCULATE (
        MAX ( 'dps_contact_role'[contactidentity_id] ),
        FILTER (
            'dps_contact_role',
            'dps_contact_role'[cutomer_id] = EARLIER ( 'dps_customer'[id] )
        )
    )
RETURN
    IF ( _ciid = BLANK (), "Contact Missing", "Contact OK" )

yingyinr_0-1669626774747.png

In addition, you can review the blogs below to know the applied scenario of RELATED function...

RELATED function

Power BI RELATED Function

Best Regards

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

View solution in original post

4 REPLIES 4
v-yiruan-msft
Community Support
Community Support

Hi @Anonymous ,

You can created a calculated column as below in the table 'dps_customer' to get it, please find the details in the attachment.

Customer Contact =
VAR _ciid =
    CALCULATE (
        MAX ( 'dps_contact_role'[contactidentity_id] ),
        FILTER (
            'dps_contact_role',
            'dps_contact_role'[cutomer_id] = EARLIER ( 'dps_customer'[id] )
        )
    )
RETURN
    IF ( _ciid = BLANK (), "Contact Missing", "Contact OK" )

yingyinr_0-1669626774747.png

In addition, you can review the blogs below to know the applied scenario of RELATED function...

RELATED function

Power BI RELATED Function

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

@v-yiruan-msft HUGE thanks!! That solution worked perfectly 🙂

lbendlin
Super User
Super User

If you sit at the M side of a M:1 relationship you can use RELATED  as it will always return one or zero results.  For all other scenarios you need to use RELATEDTABLE  and then boil the result down to a scalar value.

Anonymous
Not applicable

thanks @lbendlin 

 

I sit on 1 side, not M (that's the problem). But how could I use RELATEDTABLE in this case? if you could help with that

 

Thanks

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.