Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Measure based on report level filter

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 ...
Need Help
  • d_gosbell's avatar
    d_gosbell
    7 years ago

    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's avatar
Anonymous
Not applicable
7 years ago

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.