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

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's avatar
d_gosbell
Super User
7 years ago

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

    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.