Show the number of connected fields in another table

[AliceW]

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

[amitchandak · Accepted solution]

@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

[AliceW]

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]

Hi @AliceW ,

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

Thanks,

Pragati