Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to 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.
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
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.
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
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.
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |