Forum Discussion

evgeniam's avatar
evgeniam
Frequent Visitor
6 years ago
Solved

Dax Measure to Filter Visuals Based on Slicer

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!
Need Help
  • v-lionel-msft's avatar
    v-lionel-msft
    6 years ago

    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.

     

2 Replies

  • v-lionel-msft's avatar
    v-lionel-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      evgeniam
      Frequent Visitor
      6 years ago

      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.