Dax Measure to Filter Visuals Based on Slicer

[evgeniam]

I need to write a Dax measure to use as a filter working in the background based on input from a slicer. 

Another option would be to have two slicers, but I am afraid this would confuse the users. 

I tried using the following:

Secondary Workgroup Selection =

var Workgroup = ALLSELECTED(Employee[Workgroup])

RETURN

IF(

MAX(RevenueDetail[Secondary Workgroup]) = Workgroup ||

ISBLANK(MAX(Revenue Detail[Secondary Workgroup])),1,0)

It works, but only if Secondary Workgroup is included in the table visual. It doesn;t seem to work when I tried to apply it to bar charts and other visuals on the page (I guess because it works at row-level). 

Workgroup and Secondary Workgroup come from two different tables, many to many relationship, on EmployeeID. The EmployeeID alone is not sufficient to identify Secondary Workgroup. 

I appreciate any suggestions!

[v-lionel-msft · Accepted solution]

Hi @evgeniam ,

I did these steps.
Is the slicer you want?
What columns did you add to the table?

Then, I tried to reconstruct your measure, but I didn’t quite understand the conditions in the formula:

          MAX(RevenueDetail[Secondary Workgroup]) = Workgroup ||  ISBLANK(MAX(Revenue Detail[Secondary Workgroup]))

For example:
if [EmployeeID] = 2,
[Workgroup] = 12 || 14,
[Secondary Workgroup] = 12 || 14,
How do you want to compare these two values?

I created a measure like this:

Measure 4 = 
VAR X =
CALCULATE(
    MAX( [Workgroup]),
    ALLEXCEPT(
        Employee,
        Employee[EmployeeID ]
    )
)
VAR y = 
CALCULATE(
    MAX('Revenue Detail'[Secondary Workgroup]),
    ALLEXCEPT(
        'Revenue Detail',
        'Revenue Detail'[EmployeeID]
    )
)
RETURN
IF(
    ISFILTERED(Employee[Workgroup]),
    IF(
        X = y || ISBLANK(y),
        1,0
    )
)

If it’s not true, please tell me more specific information and give me a sample data model.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[evgeniam]

Thank you so much for your time, @v-lionel-msft 
I think your solution works as I ended up creating something similar. 

Staffing Workgroup Selection =

var Workgroup = ALLSELECTED(Employee[Workgroup])

RETURN

IF(

MAX(RevenueDetail[SecondaryWorkgroup]) = Workgroup ||

ISBLANK(MAX(RevenueDetail[SecondaryWorkgroup])),1,0)

I ended up using this measure as a filter.