cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

Filled Map Visualisation and IF function ?

Hello,

 

I'm a newbie trying to do some practice and skill up with power BI but I've got a bit stumped on a visualisation I'm trying to create.

 

I have two spreadsheets that I've brought into Power BI.

 

The first is a budget which provides a forecast of the growth of some savings - the columns are 'Start of Month', 'Savings - Total', and 'Max House Price' calculated with a formula based on the value in total savings. As a row data example: 1st April 2021 / £21,337 / £262,675. These savings, and therefore max house price, increase each month / row.

 

The second spreadsheet is some data I've pulled from a website about average house prices in locations in England. The columns are 'Location', and 'Average House Price'. I've also made a new column with ", England" affixed to the location so that Power BI knows it's specific location (e.g. Boston, England, not Boston in the USA). 

 

What I'm trying to do is have the filled map visualisation display all affordable locations given a selected date using a slicer or filter (?) (from the "Start of Month" column).

 

For example, I filter by 1st April 2021 and the map displays all locations where average house price > £262,675. ie all affordable locations based on total projected savings on that date.

 

I'm sure this is something that power BI can do but I can't seem to work it out. Something to do with the relationships ? Any help would be much appreciated! 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Filled Map Visualisation and IF function ?

Hi , @pothos 

Sorry for the late reply.

Please try to create a measure and applied it to visual filter pane as seen in "Duplicate of Page 2" (you don't need to establish relationships between tables):

 

 

Visual control = 
var max_price = SELECTEDVALUE('Budget Data - current'[Maximum Affordable House Price])
var a =IF(SELECTEDVALUE('England-annual-price-change-by-local-authority-2020-03'[House Price])>max_price,1,0) 
return a

 

 

45.png

 pbix attached

 

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
Highlighted
Community Support
Community Support

Re: Filled Map Visualisation and IF function ?

Hi, @pothos 

Sample data and expected output would help tremendously.
Could you please share your PBIX/some sample data excel and expected result to me if you don't have any Confidential Information?
You can upload your pbix file to OneDrive and share it with me if it is convenient.

 

Best Regards,
Community Support Team _ Eason

Highlighted
New Member

Re: Filled Map Visualisation and IF function ?

Thanks! Have sent you a link with the data. Hopefully my problem makes a bit more sense now.

Highlighted
Community Support
Community Support

Re: Filled Map Visualisation and IF function ?

Hi , @pothos 

Sorry for the late reply.

Please try to create a measure and applied it to visual filter pane as seen in "Duplicate of Page 2" (you don't need to establish relationships between tables):

 

 

Visual control = 
var max_price = SELECTEDVALUE('Budget Data - current'[Maximum Affordable House Price])
var a =IF(SELECTEDVALUE('England-annual-price-change-by-local-authority-2020-03'[House Price])>max_price,1,0) 
return a

 

 

45.png

 pbix attached

 

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors