Countries belonging to different groups and need to be used as filters

[PowerNewbieGR_1]

Dear all,

I feel that my issue can be described as a geohierarchy problem but I believe it is different to the one described in this post : 

Create geographical hierarchy - Microsoft Fabric Community

So here it is: I have the attached table (Excel tabular format) which shows a column of countries and next to it another column with a two letter code for each country. Next to these, I have a column with EU member countries, another one with EEA members and another one with BRICs members. There are also a few countries (USA, Singapore) that are not members to said institutions. What I need is to be able to do is to use these groups -and the countries not belonging in any of these- in order to filter other tables that contain macroeconomic numerical data on these countries especially in bubble chartw, line/combo charts as well as matrix and table visuals. Is it possible ? Is it better if I redesign the Excel table in some other way so as to better prepare it for importing in Power BI? I can also send the .pbix if that is what's needed.

Thanks a lot

Country Name	Country Code	EU Member	EEA Member	BRICs Member
Austria	AT	EU	EEA
Belgium	BE	EU	EEA
Bulgaria	BG	EU	EEA
Cyprus	CY	EU	EEA
Czech	CZ	EU	EEA
Deutschland	DE	EU	EEA
Denmark	DK	EU	EEA
Estonia	EE	EU	EEA
Ellas	EL	EU	EEA
Espana	ES	EU	EEA
Finland	FI	EU	EEA
France	FR	EU	EEA
Hrvatska	HR	EU	EEA
Hungary	HU	EU	EEA
Ireland	IE	EU	EEA
Iceland	IS		EEA
Italy	IT	EU	EEA
Lichtenstein	LI		EEA
Lithuania	LT	EU	EEA
Luxembourg	LU	EU	EEA
Latvia	LV	EU	EEA
Malta	MT	EU	EEA
Netherlands	NL	EU	EEA
Norway	NO		EEA
Poland	PO	EU	EEA
Portugal	PT	EU	EEA
Romania	RO	EU	EEA
Sweden	SE	EU	EEA
Slovenia	SI	EU	EEA
Slovakia	SK	EU	EEA
USA	US
United Kingdom	UK		EEA
Switzerland	CH		EEA
Japan	JP
Singapore	SG
Brazil	BR			BRICS
Russia	RU			BRICS
India	IN			BRICS
China	CN			BRICS

[Anonymous · Accepted solution]

Hi @PowerNewbieGR_1 ,

Regarding your first question, the picture is that you are populating the visual with quick measures.

You should manually create a measure and replace the quick measure.(Every page visual needs to replace the quick measure).

Measure 3 = 
IF( SELECTEDVALUE('EuroCountries Available Funds and Deposits'[Country Name]) IN VALUES('Table'[Country Name]),
    SUM('EuroCountries Available Funds and Deposits'[Available Protection Funds to Total Deposits Coverage Ratio])
)

If you think the workload is too much or you don't know enough about DAX, I suggest you create a many-to-many relationship.

This way you don't need to create measure ​​manually, you can use quick measures directly.

Best Regards,
Wenbin Zhou

[Anonymous]

Hi all,Thank you for your quick reply, I will add more.

Hi @PowerNewbieGR_1 ,

I think you need to adjust the table to use it for filtering with slicers.

The Table data is shown below:

Please follow these steps:

1.Delete unneeded column 'Country code'.

2.Merge selected columns

3.Split column.

4.Use the following DAX expression to create a table

Table = 
VAR _table = 
    ADDCOLUMNS('Φύλλο1',"1",VAR _isAMember = CALCULATE(COUNTROWS(FILTER('Φύλλο1',[Merged] = BLANK())),ALLEXCEPT('Φύλλο1','Φύλλο1'[Country Name])) RETURN IF(_isAMember = 3,"Not a member",[Merged]))
RETURN SELECTCOLUMNS(FILTER(_table,[1] <> BLANK()),"Country Name",[Country Name],"1",[1])

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PowerNewbieGR_1]

Dear @Anonymous ,

I tried to replicate your steps however on Step 3 Split Column - after hitting the Save Icon (to save the changes including the Split Column)- I get the following message in the Power Query Editor : 

"Column "Country Name" in Φύλλο1 (i.e. Sheet1) already has a double value for the term "Austria" which is not allowed for columns one the one side of a "many to one" relationships or for columns which are used as primary key in a table".

I did inactivate all relationships leading to Φύλλο1 but still the problem persists.

Any ideas what I can do ?

Thanks once more to you as well as @newellaa and @foodd for trying to help me.

[Anonymous]

Hi, @PowerNewbieGR_1 

Please delete all relationships and then perform the above steps.

When you import the data of this table, Power BI automatically creates a one-to-many relationship for you.(When you complete the steps I provide, the table relationship will become many-to-many, which conflicts with the one-to-many created previously.)

But if you do not modify the table structure, you need to create three slicers to filter by country based on organization.(This can indeed filter data, but as the number of organizations increases, more slicers need to be created.)

This is the modified table structure. You only need to create a slicer. You can create a many-to-many relationship between tables, but I don't recommend it. You only need to use the IF function to simply modify your measure value to complete the filtering.

Something like this.

Measure = 
IF( NOT ISFILTERED('Table'[Group]),
    SUM('EuroCountries Available Funds and Deposits'[Available Protection Funds to Total Deposits Coverage Ratio]),
    IF(SELECTEDVALUE('EuroCountries Available Funds and Deposits'[Country Name]) IN VALUES('Table'[Country Name]),
    SUM('EuroCountries Available Funds and Deposits'[Available Protection Funds to Total Deposits Coverage Ratio])
    )
)

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PowerNewbieGR_1]

Dear @Anonymous ,

I did follow your steps and created Measure2 using the syntax you send me.After that I did the following :

a) As an example I tried to filter the Matrix chart named "Matrix of Protection Funds to Total Deposits Coverage Ratio" by using the slicer that you've sais. The result was that I have filtered the slicer itself but not the Matrix chart. Probably it is some easy extra step that I dont know how to perform.So please check and help.

b) Also if I am judging correctly from the syntax of the measure does it mean that for each and every chart that I would like to filter I will have to create a new measure like the one you suggested ? or is it going to work for all charts simply by using (copying) the same slicer with the CountryName and Group columns ?

I know that these are probably basic questions that I should have known the answers, but it is really my first attempt with Power BI.

I am attaching a link to the copy of the Power BI file

https://we.tl/t-U0QiDXuOgS

Thanks alot once more to you @Anonymous and everybody else who helps.

[Anonymous · Accepted solution]

Hi @PowerNewbieGR_1 ,

Regarding your first question, the picture is that you are populating the visual with quick measures.

You should manually create a measure and replace the quick measure.(Every page visual needs to replace the quick measure).

Measure 3 = 
IF( SELECTEDVALUE('EuroCountries Available Funds and Deposits'[Country Name]) IN VALUES('Table'[Country Name]),
    SUM('EuroCountries Available Funds and Deposits'[Available Protection Funds to Total Deposits Coverage Ratio])
)

If you think the workload is too much or you don't know enough about DAX, I suggest you create a many-to-many relationship.

This way you don't need to create measure ​​manually, you can use quick measures directly.

Best Regards,
Wenbin Zhou

[PowerNewbieGR_1 · Accepted solution]

After your last post I got it now.

Much appreciated

Thanks a lot 

[newellaa]

I pulled the table in and joined to my data. I also created another field to allow filtering on the organization.

Organization = IF(LEN(Geography[BRICs Member]) <> 0, Geography[BRICs Member], IF(LEN(Geography[EEA Member]) <> 0, Geography[EEA Member], IF(LEN(Geography[EU Member]) <> 0, Geography[EU Member], "None")))

I could then filter other visuals based on Coountry Code, Country Name and Organization. 

Maybe I'm missing what you're trying to achieve.

[PowerNewbieGR_1]

Hi @newellaa 

I was about to send via dropbox my .xlsx and .pix files following @foodd advice, when your reply arrived.

Judging from the Filters screenshot, i believe what you did, is just what I wanted. However being a Power Bi newbie I am not sure how to do this. Anyway below is the link to a WeTransfer folder that includes

a)my .xlsx file (which probably needs some rearranging for it to be imported in Power Bi but I am not sure as to what)

b) v5.pbix is the version of the .pbix that I was working. The charts (especially the line charts and the bubble charts) in this, are the ones that I would like to be able to filter based on the above fields.

c) "Protection of Bank Deposits" my current version that I will be working with -and doesn't have all the visuals yet- but the aim is the same as before.

https://we.tl/t-FmHDzqd0z7

Thanks a lot

p.s. all data involved is derived from open sources , so ...no worries about dagta sensitivity 

[newellaa]

Unfortunately our company restricts access to WeTransfer. 

What you need to do is straight forward. Copy the country table and past into the Enter Data function from the Home tab.

From the model view create a join from the new table to your fact data on country code.

Go back to the report view and you can use the new table columns as filters.