cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
epa095
Frequent Visitor

Append data, filter one of the tables

I have two seperate tables, roads and shops, both containing lat/long coordinates. I want to plot these two tables in the same map. This has been asked many times before, and the answer seems to be to append them into the same table and plot this table. But I also want to be able to filter the roads by some property on them (e.g. speed-limit), but still have all elements of the other table (shops) be present on the map.

 

I have tried both appending the tables into one table with many columns, and I have tried creating a "road-index" and "shop-index" column, and then have the combined table contain lat/long/road-index/shop-index, and use releations between this and the original tables. In both cases, if I create a slizer on speed-limit, only roads are presented on the map.

Is there way to have the filter only act on the intended "subtable"?

 

 

1 ACCEPTED SOLUTION
epa095
Frequent Visitor

Thanks @lbendlin !

The size hack is a good idea. It is a bit unfortunate that it only works on maps, and is not a generall solution to filter the union of two sets, but it does solve the current problem:-D

 

I ended up with sometthing like this. For both roads and shops I first make a "Index" column. Then I append them together in All_coordinates. Now I can filter in the roads / shops, and only the active one will get size 1, everyone else will get size None (or whatever the if returns in that case, the effect is anyway that they dont show up). I define the measue "size" and use it as the size in the map.

 

size =
VAR chosentroads = VALUES ( roads[roads_index] )
VAR chosenlocatons = VALUES ( shops[shops_index] )

VAR result =
IF (
SELECTEDVALUE(All_coordinates[roads_index]) IN chosenroads || SELECTEDVALUE(All_coordinates[shops_index]) in chosenlocatons, 1
RETURN
result

View solution in original post

3 REPLIES 3
epa095
Frequent Visitor

Thanks @lbendlin !

The size hack is a good idea. It is a bit unfortunate that it only works on maps, and is not a generall solution to filter the union of two sets, but it does solve the current problem:-D

 

I ended up with sometthing like this. For both roads and shops I first make a "Index" column. Then I append them together in All_coordinates. Now I can filter in the roads / shops, and only the active one will get size 1, everyone else will get size None (or whatever the if returns in that case, the effect is anyway that they dont show up). I define the measue "size" and use it as the size in the map.

 

size =
VAR chosentroads = VALUES ( roads[roads_index] )
VAR chosenlocatons = VALUES ( shops[shops_index] )

VAR result =
IF (
SELECTEDVALUE(All_coordinates[roads_index]) IN chosenroads || SELECTEDVALUE(All_coordinates[shops_index]) in chosenlocatons, 1
RETURN
result

Hi @epa095,

Thank you for sharing your workaround and DAX expressions. I think they will help others who faced similar scenarios.
Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
lbendlin
Super User
Super User

Here is a way to do that, sort of.  Instead of hiding the data points you can manipulate their size and make them nearly invisible by setting them to 1 point.

 

There is one small niggle - at least one road needs to be selected for this trick to work.

 

See attached.

lbendlin_0-1642347671313.png

 

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors