Reply
AliceW
Impactful Individual
Impactful Individual
Partially syndicated - Outbound

Show the number of connected fields in another table

Hello fellow Power BI users,

I have two tables, connected by a one-to-many relationship. Opps (1) and Agreements (many).

One Opp can have 0, 1, or several Agreements.

I'd like to have, in the Opps table, a column showing the number of related agreements.

I've tried RELATED, FIRSTVALUENOTBLANK, COUNTX... nothing works. The cross-filter direction is set to 'both'.

However, due to (probably) the large number of tables connected to Agreements, if I add a page filter Agreement ID = BLANK, nothing is displayed.

Please help.

Thank you,

Alice

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

Syndicated - Outbound

@AliceW , copy from 1-M side not blank means exits as new columns

 

Item Name = RELATED('item'[Brand])

is item = if(isblank([Item Name]),"No found", "found")

is item = if(isblank(RELATED('item'[Brand])),"No found", "found")

 

you can also use like

City Name = maxx(FILTER(geography,geography[City Id]=Sales[City Id]),geography[City])

 

from 1 to M

 

City Name = Countx(FILTER(Sales,geography[City Id]=Sales[City Id]),Sales[City])

is item = if(isblank([City Name]),"No found", "found")  // you can keep formula in a var and do in same formula

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

Syndicated - Outbound

@AliceW , copy from 1-M side not blank means exits as new columns

 

Item Name = RELATED('item'[Brand])

is item = if(isblank([Item Name]),"No found", "found")

is item = if(isblank(RELATED('item'[Brand])),"No found", "found")

 

you can also use like

City Name = maxx(FILTER(geography,geography[City Id]=Sales[City Id]),geography[City])

 

from 1 to M

 

City Name = Countx(FILTER(Sales,geography[City Id]=Sales[City Id]),Sales[City])

is item = if(isblank([City Name]),"No found", "found")  // you can keep formula in a var and do in same formula

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
AliceW
Impactful Individual
Impactful Individual

Syndicated - Outbound

Thank you, Amit. I used a short version of what you wrote, as I wasn't sure how you meant it to be used (table, measure, column?).

City Name = maxx(FILTER(geography,geography[City Id]=Sales[City Id]),geography[City])

 

It shows what lines do have a value and what not - perfect.

You rock!

Pragati11
Super User
Super User

Syndicated - Outbound

Hi @AliceW ,

 

Can you provide some sample data for both of your tables please?

 

Thanks,

Pragati

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

avatar user

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

Check out the March 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

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

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)