Measure based on report level filter

[Anonymous]

Hi all, relatively new to Power BI but familiar with DAX.

I am trying to create some logic to check and see if a slicer or a report level filtered value matches a value in one or another columns in my table. I would like to use this logic to create a measure to apply to a visual level filter.

Example:

Table1

Sales Rep#	Date	Manager Name	Account Owner	Sales
1234	Mar-19	John Smith	Jane Doe	34029
3425	Mar-19	Jane Doe	Roger Davis	23045
1234	Mar-19	Jane Doe	John Smith	12023
5432	Mar-19	Roger Davis	Roger Davis	40003

In my report I will have a report level filter that allows me to select one more multiple names.

I would like to create a measure for a visual that checks two columns of data for the selected names so I can apply that to a visual level filter.

So If I select John Smith, my measure would flag the two lines below and total them.

Sales Rep#	Date	Manager Name	Account Owner	Sales
1234	Mar-19	John Smith	Jane Doe	34029
3425	Mar-19	Jane Doe	Roger Davis	23045
1234	Mar-19	Jane Doe	John Smith	12023
5432	Mar-19	Roger Davis	Roger Davis	40003

[Anonymous]

I apologize for some of the ambiguity but the file is partially protected and cannot be shared. I can shed some light on what I am trying to accomplish and hopefully this helps you help me:

I have 4 folders of csv files that I am importing into the data model to form the structure of all my relationships. There are 4 fields which are common to almost all of the files, except 1.

The fields are things like "campaign", "account", "month of year" etc.

I have created relationships between the file's data models and dynamically generated tables that extract unique values for using in report level filters and slicers.

The problem is that one file doesnt have a crucial field that I am filtering in one single column, but instead sporadically placed in one of two columns. So if I wanted to filter by "campaign", I would have to check whether that value that is selected in the report level filter or slicer is on a specific row in one of two places, and filter the rows in the visual by a flag of some kind. The bigger challenge is the "OR" logic needed. I need it to test for "either in column A or column B". Since most filtering operates on an "AND" structure it does not wind up showing the correct data. 

I have attempted to use a filtering measure but I have not been successful at referencing a filter or slicer value to use in a row calculation.

The datasets are also extremely large and will be growing. I do not know if this matters or not, but in case it does there you go. The successful version of this measure would allow me to calculate multiple items within that row based on filtered values from this "OR" filter.

[Ashish_Mathur]

Hi,

Do you simply want a total in a card visual where the person chosen in the slicer is available in any of the 2 columns?

[d_gosbell · Accepted solution]

To achieve this you will need a new table that has a distinct list of all names from both the owner and manager tables.

You can do this by creating a calcuated table with the following code

Person = DISTINCT(UNION(DISTINCT('Table'[Account Owner]),DISTINCT('Table'[Account Owner])))

Note: I would rename the "Account Owner" column in this table after you've created it to something like "Person Name" 

Then you could create a slicer on this new table and create a measure like the following which will do the OR filter on either column. Then when ever you using this measure with the Person Name slicer it will filter on both Owner and Manager names.

Total Sales = 
CALCULATE( 
   SUM('Table'[Sales]), 
   FILTER(ALL('Table'[Account Owner],'Table'[Manager Name]), 
       'Table'[Account Owner] in values(Person[Person Name]) 
        || 'Table'[Manager Name] in VALUES(Person[Person Name])
   )
)

[Anonymous]

I wound up making a hybrid to this approach and simply building a measure to accomplish this instead of using the measure to help filter the data. Apparently Power BI does not like it when you do this and does not retain filter context when evaluating a measure inside a filter.

[Anonymous]

Thank you for your reply. I currently have the data stored in a data model import table and I already have a linked unique list of values derived from the data in a seperate table as you suggested.

The linked values are the values being filtered, which are in turn filtering the other tables through the relationships.

In this particular case, the particular table in question has multiple columns where that data exists, so there is no direct relationship setup.

I am currently testing this boolean measure and wondered if this would work without creating a new table?

FlagFilteredCampaign = if(OR(FILTERS('Linked Name Table'[Name List]) IN values('Table'[Account Owner]),FILTERS(Linked_Name_Table[Name List]) IN values('Table'[Manager Name]),"Y","N")

[d_gosbell]

---

---

I'm a little confused what you mean by the tables are "linked" but then you say there are no direct relationships

But if you have an existing table you can use that. I don't think the syntax of your measure looks valid, but you could do something just counting the intersecting rows between the various columns.

FlagFilteredCampaign = 
if(
    countrows(
       INTERSECT(VALUES('Person'[Person Name]) , 
         union(values('Table'[Account Owner]), values('Table'[Manager Name])))
    )> 0,
"Y",
"N")

If there was an active relationship filtering this table you can effectively turn it off using the CROSSFILTER function

FlagFilteredCampaign = 
  CALCULATE( 
    if(
      countrows(
         INTERSECT(VALUES('Person'[Person Name]) , 
           union(values('Table'[Account Owner]), values('Table'[Manager Name])))
      )> 0,
    "Y",
    "N")
  ,CROSSFILTER('Table'[Manager Name],Person[Person Name],None)
)

[Anonymous]

I did try this method and it "appeared" to work. When I applied this measure filter to a chart that did not include the two columns as fields or values it did not show the correct data. The report level filters are holding but it appears that if my visual does not contain the two columns being used in the measure, it does not filter the visual properly.