- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

@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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

@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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Helpful resources
Subject | Author | Posted | |
---|---|---|---|
Anonymous
| 05-22-2024 12:40 PM | ||
01-17-2024 01:51 PM | |||
05-21-2024 01:50 PM | |||
09-12-2022 09:16 AM | |||
06-16-2024 03:22 AM |
User | Count |
---|---|
141 | |
115 | |
84 | |
63 | |
48 |