Forum Discussion

Blablabla's avatar
Blablabla
Advocate I
2 years ago
Solved

Add a filter to a DAX inside a define

I have this code using DEFINE :

 

 

DEFINE

    VAR __DS0Core =

        SUMMARIZECOLUMNS (

            'Employees'[Matri],
'Employees'[SDAP type],
'Employees'[Recorded communications],
'Department'[Label]

        )



EVALUATE

FILTER (

    ADDCOLUMNS (

        __DS0Core,

        "AP_SUV_not_Recorded",

            IF (

                (

                    NOT ( ISBLANK ( 'Employees'[SDAP type] ) )

                        || 'Employees'[SBSDAP type] = "ABC"

                        || 'Employees'[SBSDAP type] = "BB"

                )

                    && 'Employees'[Recorded communications] = "No",

                1,

                0

            )

    ),

    'Department'[Label] = "HR"

)

ORDER BY

    'Employees'[Matri] DESC,

    

 

 

I want to add a filter as a condition, 'Employees'[Matri] = "9999".

How to do this in the code ?

 
  •  

    You already have a similar filter on the department :

     

    DEFINE
    
        VAR __DS0Core =
    
            SUMMARIZECOLUMNS (
    
                'Employees'[Matri],
    			'Employees'[SDAP type],
    			'Employees'[Recorded communications],
    			'Department'[Label]
    
            )
    
     
    
    EVALUATE
    
    FILTER (
    
        ADDCOLUMNS (
    
            __DS0Core,
    
            "AP_SUV_not_Recorded",
    
                IF (
    
                    (
    
                        NOT ( ISBLANK ( 'Employees'[SDAP type] ) )
    
                            || 'Employees'[SBSDAP type] = "ABC"
    
                            || 'Employees'[SBSDAP type] = "BB"
    
                    )
    
                        && 'Employees'[Recorded communications] = "No",
    
                    1,
    
                    0
    
                )
    
        ),
    
        'Department'[Label] = "HR" ,
        'Employees'[Matri] =9999
    
    )
    
    ORDER BY
    
        'Employees'[Matri] DESC,
    
        

     

    This is an improved version :

    DEFINE
        VAR __DS0Core =
            SUMMARIZECOLUMNS (
                'Employees'[Matri],
                'Employees'[SDAP type],
                'Employees'[Recorded communications],
                'Department'[Label]
            )
    
    EVALUATE
    VAR FilteredData =
        FILTER (
            __DS0Core,
            'Department'[Label] = "HR" &&
            'Employees'[Matri] = 9999
        )
    
    VAR Result =
        ADDCOLUMNS (
            FilteredData,
            "AP_SUV_not_Recorded",
            IF (
                (
                    NOT ( ISBLANK ( 'Employees'[SDAP type] ) ) ||
                    'Employees'[SDAP type] = "ABC" ||
                    'Employees'[SDAP type] = "BB"
                ) &&
                'Employees'[Recorded communications] = "No",
                1,
                0
            )
        )
    
    RETURN
        Result
    ORDER BY
        'Employees'[Matri] DESC

2 Replies

  •  

    You already have a similar filter on the department :

     

    DEFINE
    
        VAR __DS0Core =
    
            SUMMARIZECOLUMNS (
    
                'Employees'[Matri],
    			'Employees'[SDAP type],
    			'Employees'[Recorded communications],
    			'Department'[Label]
    
            )
    
     
    
    EVALUATE
    
    FILTER (
    
        ADDCOLUMNS (
    
            __DS0Core,
    
            "AP_SUV_not_Recorded",
    
                IF (
    
                    (
    
                        NOT ( ISBLANK ( 'Employees'[SDAP type] ) )
    
                            || 'Employees'[SBSDAP type] = "ABC"
    
                            || 'Employees'[SBSDAP type] = "BB"
    
                    )
    
                        && 'Employees'[Recorded communications] = "No",
    
                    1,
    
                    0
    
                )
    
        ),
    
        'Department'[Label] = "HR" ,
        'Employees'[Matri] =9999
    
    )
    
    ORDER BY
    
        'Employees'[Matri] DESC,
    
        

     

    This is an improved version :

    DEFINE
        VAR __DS0Core =
            SUMMARIZECOLUMNS (
                'Employees'[Matri],
                'Employees'[SDAP type],
                'Employees'[Recorded communications],
                'Department'[Label]
            )
    
    EVALUATE
    VAR FilteredData =
        FILTER (
            __DS0Core,
            'Department'[Label] = "HR" &&
            'Employees'[Matri] = 9999
        )
    
    VAR Result =
        ADDCOLUMNS (
            FilteredData,
            "AP_SUV_not_Recorded",
            IF (
                (
                    NOT ( ISBLANK ( 'Employees'[SDAP type] ) ) ||
                    'Employees'[SDAP type] = "ABC" ||
                    'Employees'[SDAP type] = "BB"
                ) &&
                'Employees'[Recorded communications] = "No",
                1,
                0
            )
        )
    
    RETURN
        Result
    ORDER BY
        'Employees'[Matri] DESC