Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am doing some analysis of restaurant density. i have create a list of restaurants and their postcode district (the AL1 bit). i have easily counted how many competitors to each restaurant there are in the SAME postcdoe district using a calcualted column
=CALCULATE(
[Count],
FILTER(
Restaurants,
Restaurants[Postcode District]=EARLIER(Restaurants[Postcode District]) &&
Restaurants[Chain]<>EARLIER(Restaurants[Chain])
)
)
Now i want to try to count how many others are in neighbouring districts. i have created another table caleld Postcode_NearbyDistricts that contains 2 columns, firstly the district then the neighbouring district. so for each district there might be say 10 rows. for example for District L2
Postcode District Nearby District
L2 | CH25 |
L2 | L1 |
L2 | L3 |
L2 | L5 |
L2 | L67 |
L2 | L69 |
L2 | L70 |
L2 | L71 |
L2 | L73 |
L2 | L74 |
My restaurant table looks like this
Branch Postcode District
Liverpool | L1 |
Albert Dock | L3 |
Aughton | L39 |
Otterspool | L17 |
Liverpool | L1 |
Liverpool | L1 |
Liverpool | L2 |
Liverpool | L1 |
Liverpool | L37 |
So for a row on the restaurant table i need to look up the nearby district rows and then filter the restaurant table for the 10 that match and count the rows. i just can't work out how to do this if anyone can help. There are multiple districts on both tables so cant use any relationship
Much appreciated
Mike
Hi @masplin,
Based on my test, you can refer to below steps:
1.I have entered some sample data to test for your problem in below pictures.
2.Create a calculated column to count the relative district.
Related district number = COUNTROWS(FILTER(RELATEDTABLE('Postcode District Nearby Distri'),Restaurant[District]='Postcode District Nearby Distri'[District]))
3.Create a Table visual and add the [Name], [District] and [Related district number] field and you can see the result.
You can also download the PBIX file to have a view.
Regards,
Daniel He
Sorry maybe wasnt clear as problem is more complicated than that. In my example I have one restaurant that is in L2
L2 has 10 neighbouring postcode districts. Now i need to take these 10 districts and go back to the restaurant table and count how many are in those. so in my example the restaurants in L1 & L3 are neighbouring and there are 5 of those. The restaurants in L2,L39, L17 and L37 are no neighbouring so dont count. The answer is therefore 5.
I tried this code, but it is a circular reference, but logically seems sort of right process
In middle CALCULATE I tried to get a table of values of the nearby districts for the specific row I was on.
Then FILTER the restaurants table for just the districts in the table found from the NearbyDistricts table that are related
Then SUM the [count] (just a row count measure) over the fitlered table
=CALCULATE( SUMX( FILTER(Restaurants, [Postcode District]= CALCULATE( VALUES(Postcode_NearbyDistrict[Nearby District]), Restaurants[Postcode District]=Postcode_NearbyDistrict[Postcode district] ) ) , [Count] ))
I have hacked this together in excel usingh a terrible method. I have all my restaurants down the side and all the possible postcode districts with neighbouring ditricts underneath along the top. Massive matrix looking up each restaurants related districts then a formula in each column to go and count how many for each of the related ditricts. Then sum the row. It works but its rubbish
formula is =IF($D4=F$2,SUMIF($D:$D,F$3,$E:$E),""). First column is D, first row is 2. so if Restaurant district = top row district then use the 2nd row of ditricts to sum the count column where the districts match
Postcode DistrictCount
AB10 | AB10 | AB10 | AB10 | ||
AB1 | AB11 | ||||
W1B | 1 | ||||
NW3 | 1 | ||||
SW3 | 1 | ||||
EC3V | 1 |
One question is how come you can use RELATEDTABLE when it is a Many to Many relationship? Is this because as a clauted column the relationship is context filtered to only have one value coming from the Restaurant table? if so that would mean your idea of RELATEDTABLE gnerates the table of values needed to feedback to the restaurant table. I suspect you still get a circular reference.
Mike
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
94 | |
74 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |