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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.