Forum Discussion

naelske_cronos's avatar
naelske_cronos
Icon for Advocate II rankAdvocate II
1 year ago
Solved

DAX - remove filter and add it again after full aggregation

Hello

 

I have a DAX question about remove filters:

 

This is my measure:

_AverageMachineHoursV1 = 
CALCULATE ( 
    AVERAGEX (
        FILTER (
            DIM_Date,
            YEAR ( [Date] )
                = YEAR ( [_SelectedDate] ) - 1
                && NOT ( [DayOfWeekNumber] IN { 6, 7 } )
        ),
        CALCULATE ( SUM ( FACT_Productivity[MHOURS] ) )
    ),
    REMOVEFILTERS ( DIM_Machine ),
    REMOVEFILTERS ( DIM_Department )
)

With this measure I just want a sum of the total machine hours of last year ignoring the filters on DIM_Department and DIM_Machine. I just want the average of machines hours filtered on weeknumber.

What I want is that after the result I want to keep the aggregation result but when I select a department, it only shows the machines that are related to the selected department. Like in the image below, I selected department 'B' and I expect is that machine "0000B" is removed from table but still keeping the average results. Is that possible? Even with my data model?

 

 

Thank you
Kind regards

  • You could create a measure like

    Is Visible =
    IF (
        SELECTEDVALUE ( DIM_Machine[DepartmentID] )
            IN VALUES ( DIM_Department[DepartmentID] ),
        1
    )
    

    and add that as a filter to visual, to only show when the value is 1.

2 Replies

  • You could create a measure like

    Is Visible =
    IF (
        SELECTEDVALUE ( DIM_Machine[DepartmentID] )
            IN VALUES ( DIM_Department[DepartmentID] ),
        1
    )
    

    and add that as a filter to visual, to only show when the value is 1.

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hello,

    Did the solution johnt75  offered help you solve the problem, if it helps, you can consider to accept it as a solution so that more user can refer to, or if you have other quertsions, can provide some information so that can offer more suggestion for you.

     

    Best Regards!

    Yolo Zhu