How to create a new column in a table by referring to another table column using DAX

[sharon_net]

Hi,

I am trying to create a new column 'count_of_listings', in the neighbourhood table by referring to the listing_id column in the listings_summary table where the neighbourhood property in both tables match

number_of_listings = CALCULATE(COUNT(listings_summary[id]), FILTER(neighbourhoods, neighbourhoods[Neighbourhood_Name]=listings_summary[neighbourhood]))

The above expression is throwing an error- 

A single value for column 'neighbourhood' in table 'listings_summary' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

My dataset -

Table- neighbourhoods

Neighbourhood_ID | Neighbourhood_Name

1|Westminister

2|Barnet

Table - listings_summary

listing_id| neighbourhood

1|Westminister

2|Barnet

3|Westminister

expected output

Table- neighbourhoods

Neighbourhood_ID | Neighbourhood_Name|number_of_listings

1|Westminister|2

2|Barnet|1

[sharon_net]

@Fowmy Thank you for the quick response. I'm getting this error-

A single value for column 'neighbourhood' in table 'listings_summary' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

I tried with a different sample and see the same issue

Data set 

Table-waitlist_by_neighbourhood

Area|waitlist_count

Westminister | 10

Barnet|30

Table-neighbourhoods_json

id|neighbourhood

1|Westminister

2|Barnet

Expected to add a column in the above table be infering the former table

id|neighbourhood|waitlist_count

1|Westminister|10

2|Barnet|30

rental_waitlist_count = VAR __n = waitlist_by_neighbourhood[Area]

RETURN    

 (waitlist_by_neighbourhood[waitlist_count],

    FILTER (

        neighbourhoods_json,

        neighbourhoods_json[neighbourhood] = __n

    )

)

I see this error-

A single value for column 'Area' in table 'waitlist_by_neighbourhood' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

even though there are no duplicate Area values in my dataset

Could you please tell me where I am going wrong?

[Fowmy]

@sharon_net 

number_of_listings =
VAR __n = listings_summary[neighbourhood]
RETURN    
COUNTROWS (
    FILTER (
        neighbourhoods,
        neighbourhoods[Neighbourhood_Name] = __n
    )
)