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