Forum Discussion

Usix's avatar
Usix
Frequent Visitor
9 months ago
Solved

Removefilters, all, keepfilters behavior

Hi all, I have 2 tables 

> Employees withs 2023 & 2024 salaries

> a sample of employees ID on which I want to filter my visual

 

Both have an active relationship on employee ID

 

The visual I want is a matrix, for each employee in the filtered list (table2) to calculate in 2 columns : salary evolution, evolution of the similar group.

 

so, in lines I have : 'emp filters'[ID], '2023 2024'[Job Family]
My measures are 

Evolution = DIVIDE(SUM('2023 2024'[2024.SALARY]) - SUM('2023 2024'[2023.SALARY]), SUM('2023 2024'[2023.SALARY]))
 
Same job family = CALCULATE(
                            [Evolution],
                             ALL('2023 2024'),
                             KEEPFILTERS(VALUES(('2023 2024'[Job Family])
)))
 
The thing is that when I wrote the "same family" measure by excluding filters It behave oddly : every single row got all the job families below. But now with this formula it seems to work but I'm not sure to understand why ?
1/ why the KEEPFILTERS+VALUES ?
2/ Plus, the matrix still gives me an additional row with no ID but all the job families and some numbers, I don't understand why
3/ Why in the 'same job family' measure I don't have to add an ALL('emp filters') to get the right numbers ?
 
Need Help
Show and Tell
  • amitchandak's avatar
    amitchandak
    9 months ago

    Usix , All or remove filter should be used on a column in this case like 
    Same job family = CALCULATE(
    [Evolution],
    ALL('2023 2024'[Job Family])
    ))

    KEEPFILTERS- Prevents CALCULATE from expanding the filter to ALL Job Families- refer -https://www.sqlbi.com/articles/using-keepfilters-in-dax/


    for removing blank try 

    Same job family =
IF(
    ISBLANK(SELECTEDVALUE('emp filters'[ID])),
    BLANK(),
    CALCULATE(
        [Evolution],
        ALL('2023 2024'),
        KEEPFILTERS(VALUES('2023 2024'[Job Family]))
    )
)

     

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    9 months ago

    Usix , All or remove filter should be used on a column in this case like 
    Same job family = CALCULATE(
    [Evolution],
    ALL('2023 2024'[Job Family])
    ))

    KEEPFILTERS- Prevents CALCULATE from expanding the filter to ALL Job Families- refer -https://www.sqlbi.com/articles/using-keepfilters-in-dax/


    for removing blank try 

    Same job family =
IF(
    ISBLANK(SELECTEDVALUE('emp filters'[ID])),
    BLANK(),
    CALCULATE(
        [Evolution],
        ALL('2023 2024'),
        KEEPFILTERS(VALUES('2023 2024'[Job Family]))
    )
)

     

  • rohit1991's avatar
    rohit1991
    Icon for Super User rankSuper User
    9 months ago

    Hii Usix 

     

    ALL('2023 2024') removes every filter from the table, including Employee ID and Job Family. Once everything is cleared, KEEPFILTERS( VALUES(Job Family) ) only restores the Job Family filter, but the employee-level filter from your sample list is lost. Because of this, the measure stops respecting the filtered employee subset and instead calculates evolution for the entire Job Family group. The correct approach is to remove only the Job Family filter > while keeping the employee filter intact > using:

    Same job family =
CALCULATE(
    [Evolution],
    REMOVEFILTERS('2023 2024'[Job Family])
)

    This preserves the selected employees and correctly evaluates evolution within their Job Family.

     

    • Usix's avatar
      Usix
      Frequent Visitor
      9 months ago

      Hi, but the matrix have emp. ID from the 'emp filters' table, not the emp. ID from the '2023 2024' table, and I only specified the '2023 2024' in the ALL() ... 

       

      Otherwise, I do want to calculate the evolution of all employees of the same job family, the goal is to have those columns : 

       

      emp filters.ID | '2023 2024' Job family | Evolution | Evolution of same job family group

      • rohit1991's avatar
        rohit1991
        Icon for Super User rankSuper User
        9 months ago

        Hii Usix 

         

        Even if your matrix uses emp filters[ID], the employee filter automatically flows into the '2023 2024' table through the active relationship.
        That’s why you don’t need to reference emp filters again inside the measure.

        The only filter you wanted to override was Job Family, and this is exactly does correctly.
        The employee filter remains active, the job family filter is removed, and the calculation runs for the whole job family group.

  • v-sgandrathi's avatar
    v-sgandrathi
    Icon for Community Support rankCommunity Support
    9 months ago

    Hi Usix,

     

    Thank you rohit1991 and amitchandak  for your prompt response to the query.

    Just wanted to follow up and confirm that everything has been going well on this. Please let me know if there’s anything from our end.
    Please feel free to reach out Microsoft fabric community forum.