Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
thomsog1
Frequent Visitor

Filled map with filtered values from 2 slicers

I have been given a table of countries grouped into region codes (see example Table1 below): 

 

Region Code  

Country

Region Code 1  

Germany

Region Code 1  

France

Region Code 1  

Spain

Region Code 2  

Sweden

Region Code 2  

Finland

Region Code 2  

Norway

Region Code 2  

Denmark

Region Code 3  

United States of America

Region Code 3  

Canada

Region Code 4  

Australia

Region Code 4  

New Zealand

 

Additionally I have a separate table of information related to these Region Codes and the offices which sit within them (see example Table2 below):

 

Region Code  

Office Code    

Metric 1

Metric 2

Metric 3

Region Code 1    

Office A  

800

810

290

Region Code 1    

Office B  

830

730

220

Region Code 1    

Office C  

650

440

290

Region Code 1  

Office D  

470

90

350

Region Code 2  

Office E  

560

550

970

Region Code 2  

Office F  

130

130

720

Region Code 2  

Office G  

930

810

540

Region Code 2  

Office H  

90

170

390

Region Code 3  

Office I  

830

110

260

Region Code 3  

Office J  

980

480

990

Region Code 3  

Office K  

160

650

860

Region Code 3  

Office L  

870

220

90

Region Code 4  

Office M  

930

110

150

Region Code 4  

Office N  

760

840

900

Region Code 4  

Office O  

730

800

210

Region Code 4  

Office P  

410

430

410

 

The dashboard I'm building needs to be able to compare 2 regions side by side in seperate visuals of the Metrics in Table2, but also have a map of the countries within each region also (as a single visual).

 

By editing the interactions I've been able to create 2 slicers which show all the region codes and table ability to filter different visuals depending on Slicer A and Slicer B. This means my individual visuals based on Table2 work fine.

However I am really struggling to work out how to then show a single Filled Map visual with both region code areas shown at the same time. Given the values all need to be in a single unpivoted column for a filled map, it appears like I need to create 2 filtered measures and then unpivot them into a new result...? Unfortunately I have no idea where to start as I'm still getting my head round the basics of DAX...

Any help would be brilliant, thanks in advance!

1 ACCEPTED SOLUTION

Hi @v-cazheng-msft , thanks for the suggestion.

I've just managed to get it working the now on a single filled map creating the same Table 1 three times. 2 of them connect to Silcers A & B (with the interactions set to work only on those tables and not filter each other). Within each of these tables I created measures (RegionCode_A and RegionCode_B) to return the first region code (so it would return only the slicer values).

I then used a DAX expression to create an if statement to say if the regioncode equals either of the region codes stored in the measures I created then call it True, else call it False. This was then used to filter the filled map visual.

 

IF(CONCATENATEX('Region - Mapping','Region - Mapping'[Level 1])=[RegionCode_A]||CONCATENATEX('Region - Mapping','Region - Mapping'[RegionCode])=[RegionCode_B],True,False))


It is probably a massive over complication of whats required, but it was the only way I could manage to work through the problem.

View solution in original post

5 REPLIES 5
v-cazheng-msft
Community Support
Community Support

Hi, @thomsog1 

For Slicers on report page, Power BI will take the intersection of them as the result of interaction. Therefore, If you select “Region Code 1” in Slicer A and “Region Code 3” in Slicer B, the result will be blank and none of regions will be selected in one Filled map.
You can create two Filled maps, Slicer A interacts with Filled map A and Slicer B interacts with Filled map B. In this case, Region 1 and Region 3 will display on the page at the same time.

 

Best Regards,

Caiyun Zheng

 

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-cazheng-msft , thanks for the suggestion.

I've just managed to get it working the now on a single filled map creating the same Table 1 three times. 2 of them connect to Silcers A & B (with the interactions set to work only on those tables and not filter each other). Within each of these tables I created measures (RegionCode_A and RegionCode_B) to return the first region code (so it would return only the slicer values).

I then used a DAX expression to create an if statement to say if the regioncode equals either of the region codes stored in the measures I created then call it True, else call it False. This was then used to filter the filled map visual.

 

IF(CONCATENATEX('Region - Mapping','Region - Mapping'[Level 1])=[RegionCode_A]||CONCATENATEX('Region - Mapping','Region - Mapping'[RegionCode])=[RegionCode_B],True,False))


It is probably a massive over complication of whats required, but it was the only way I could manage to work through the problem.

Hi, @thomsog1 

 

This method is very ingenious. I can't find a better way at the moment. You can consider mark it as the solution to help the other members find it more quickly.

 

Best Regards,

Caiyun Zheng

thomsog1
Frequent Visitor

Hi  sorry yeah I probably wasnt clear enough...

Table2 doesnt really relate to my issue, apologies - I use it to create seperate charts visuals for the values selected in each of my slicers. In reality it's probably more confusing to have included it in my questions at all - Apologes!

I have 2 slicers which are both of the distinct values in Table 1. Therefore when I select "Region Code 1" in Slicer A and "Region Code 3" in Slicer B I would want the filled map to show the areas for both Regions in the same map, which I think requires an output that looks like the following...

Region Code  

Country

Region Code 1  

Germany

Region Code 1  

France

Region Code 1  

Spain

Region Code 3  

United States of America

Region Code 3  

Canada


I can't use a single slicer because the client wants to be able to select both Regions independently.

Any ideas would be great - my gut feel is I need to filter Table1 twice for each slicer value (as seperate measures or a data table...?), then union them on top of each other since they will have the same structure? But I'm still fairly new to DAX so how to achieve this feels quite complicated.

MFelix
Super User
Super User

Hi @thomsog1 ,

 

Can you please explain a little bit better what you mean by:


@thomsog1 wrote:

 

However I am really struggling to work out how to then show a single Filled Map visual with both region code areas shown at the same time. Given the values all need to be in a single unpivoted column for a filled map, it appears like I need to create 2 filtered measures and then unpivot them into a new result...? Unfortunately I have no idea where to start as I'm still getting my head round the basics of DAX...


What is the final result you are trying to achieve? Do you want to have more than one metric from the table 2 in the map?

 

Have you tried to unpivot the metric in table two and used it has a slicer or a filter in your visualizations?

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.