CODEWITHSUNDEEP
sql-servert-sqlfilterpowerbidax
MoonLightFlower
MoonLightFlower

Reputation: 183

Power Bi : Filter a SQL Server table which contains a string

I have 2 tables in SQL Server :

Table Country which countains the name of the country :

enter image description here

Table Society which the name of the society and the names of the countries where the society worked :

enter image description here

In Power Bi, i have to create a filter country (US, Germany, France, UK,...) that will filter the table society :

For example, if i put "US" in the filter Country, in my matrix i will have Society A and Society B. If i put "France" in the filter Country, i will have Society B and Society C.

(My first idea was to add some binary fields "IsInThisCountry" in SQL Server then use these fields as a filter ) Something like this : CASE WHEN country LIKE '%US%' THEN 1 ELSE 0 END 'IsUS'

But the issue is if i have 50 country, i will have to create 50 filter

Upvotes: 0

Views: 157

Answers (1)

Cedersved
Cedersved

Reputation: 1025

If you have SQL Server with compatibility 130 or higher (with string_split) you can try something like this in your data model to split the delimited countries in your societies table:

;with countries as (
select 'germany' as country
union all
select 'sweden'
),
socities as (
select 'A' as society, 'germany-sweden' as countries
union all
select 'B', 'sweden'
),
societyByCountry as (
select c.society, value as Country  from socities c
cross apply string_split(c.countries, '-') s
)

select c.country, s.society from countries c
inner join societyByCountry s on s.Country = c.country

Upvotes: 1

Related Questions