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

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 ...

Need Help

Tips and Tricks

v-alq-msft

6 years ago

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.