How to build a filter for a report based on multiple columns?

[dc7669]

Company Name	N GA	E GA	S GA	W GA
ABC	X			X
DEF		X	X	X
GHI	X		X

I have a table in the above format.  I want to create a Power BI showing the company name who worked in a particular part of GA. 

I want a drop down list that say 

N GA

E GA

S GA

W GA

And then by selecting N GA, Company ABC and GHI will be displayed.  

How can I do this? 

I can create a separate list for the N/E/S/W GA list, but I don't know how it can be linked back to the above table.  

I can combine the 4 columns like below and use the Overall column as the slicer.  But, all possible combinations of N/E/S/W GA will show up as an option.  That is not good.  

Company Name	N GA	E GA	S GA	W GA	Overall
ABC	X			X	N GA; W GA
DEF		X	X	X	E GA; S GA; W GA
GHI	X		X		N GA; S GA

Any ideas? 

[v-alq-msft]

Hi, @dc7669 

If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.

Best Regards

Allan

[v-alq-msft · Accepted solution]

Hi, @dc7669 

The easiest way is to use unpivot in Query Editor. Additonally, you may also try the following steps. I created data to reproduce your scenario.

Table:

NewTable:

You may create measures as below.

NGA = 
var _companyname = SELECTEDVALUE('Table'[Company Name])
var _value = LOOKUPVALUE('Table'[N GA],'Table'[Company Name],_companyname)
return
IF(
    "N GA" in FILTERS(NewTable[OverAll])&&_value<>BLANK(),
    1
)
EGA = 
var _companyname = SELECTEDVALUE('Table'[Company Name])
var _value = LOOKUPVALUE('Table'[E GA],'Table'[Company Name],_companyname)
return
IF(
    "E GA" in FILTERS(NewTable[OverAll])&&_value<>BLANK(),
    1
)
SGA = 
var _companyname = SELECTEDVALUE('Table'[Company Name])
var _value = LOOKUPVALUE('Table'[S GA ],'Table'[Company Name],_companyname)
return
IF(
    "S GA" in FILTERS(NewTable[OverAll])&&_value<>BLANK(),
    1
)
WGA = 
var _companyname = SELECTEDVALUE('Table'[Company Name])
var _value = LOOKUPVALUE('Table'[W GA],'Table'[Company Name],_companyname)
return
IF(
    "W GA" in FILTERS(NewTable[OverAll])&&_value<>BLANK(),
    1
)

Isdisplay = 
IF(
    [NGA]=1 || [EGA]=1 || [SGA]=1 || [WGA]=1,
    1,0
)

Then you need to put the 'Isdisplay' measure in the visual level filter. Here are the results.

Best Regards

Allan

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

[BA_Pete]

Hi @dc7669

In Power Query you should be able to unpivot the region columns in your table so you have a Company column, an Attribute column which will hold all your regions, and a Value column which will have all your Xs in.
You can then slice on the Attribute (Region) column as normal.

Pete