Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

DAX Measure not displaying null values in matrix table- where we have no TEXT in a particular column

Hi Experts

 

See the following measure: i want to amend the DAX so if vw_fct_msi_icontrol_task_execution_assessments[result_rating] is empty ie. no text value in that column. then give a result in the matrix table visual. Currently i am seeing the following text values returned back from column [result_rating]

 

I want to see text string the the above column [result_rating], blank where we have no string value against

vw_fct_msi_icontrol_task_execution_assessments[assessed_on]

 

If i remove NOT from && NOT ISBLANK( vw_fct_msi_icontrol_task_execution_assessments[result_rating] ) nothing happens. 

 

_m_get_last_result_rating_scope_ctrl = 
//SWITCH in the end hides result unless we are at control level
//Used in Tab Result History -> Matrix Latest result rating and in Conditional Formatting for Background & Font

VAR org =
    SELECTEDVALUE ( vw_fct_msi_icontrol_task_execution_assessments[organization_name] )
VAR ctrl =
    SELECTEDVALUE ( vw_fct_msi_icontrol_task_execution_assessments[control_name] )
VAR result =
    MAXX (
        FILTER (
            ALLSELECTED(vw_fct_msi_icontrol_task_execution_assessments),
            vw_fct_msi_icontrol_task_execution_assessments[organization_name] = org
                && vw_fct_msi_icontrol_task_execution_assessments[control_name] = ctrl
                && NOT ISBLANK ( vw_fct_msi_icontrol_task_execution_assessments[assessed_on] )
                    && NOT ISBLANK( vw_fct_msi_icontrol_task_execution_assessments[result_rating] )
                        && vw_fct_msi_icontrol_task_execution_assessments[assessed_on] = [_m_get_max_assessed_on_scope_ctrl]
        ),
        vw_fct_msi_icontrol_task_execution_assessments[result_rating]
    )
    
RETURN
    SWITCH (
        TRUE,
        ISINSCOPE ( vw_dim_msi_icontrol_controls[control_name] ), IF ( ISBLANK ( result ), result, BLANK () ),
        // If not blank - give empty string to prevent matrix from collapsing but remove field in condiotional formatting in matrix
        ISINSCOPE ( vw_dim_msi_organization[level_5] ), IF ( NOT ISBLANK ( result ), "", BLANK () ),
        ISINSCOPE ( vw_dim_msi_organization[level_4] ), IF ( NOT ISBLANK ( result ), "", BLANK () ),
        ISINSCOPE ( vw_dim_msi_organization[level_3] ), IF ( NOT ISBLANK ( result ), "", BLANK () ),
        ISINSCOPE ( vw_dim_msi_organization[level_2] ), IF ( NOT ISBLANK ( result ), "", BLANK () ),
        ISINSCOPE ( vw_dim_msi_organization[level_1] ), IF ( NOT ISBLANK ( result ), "", BLANK () ),
        BLANK ()
    )

 

 

 

  • Anonymous's avatar
    Anonymous
    2 years ago

    Worked it out by remove line 

     && NOT ISBLANK ( vw_fct_msi_icontrol_task_execution_assessments[result_rating] )

    from the measur e

    _m_get_max_assessed_on_scope_ctrl

     gives the expected result.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    _m_get_max_assessed_on_scope_ctrl = 
    //Returns result if scope control
    //Used in Tab Result History -> Matrix ORG view
    //Used in [_m_get_last_result_rating_scope_roll_func]
    VAR org =
        SELECTEDVALUE ( vw_fct_msi_icontrol_task_execution_assessments[organization_name] )
    VAR ctrl =
        SELECTEDVALUE ( vw_fct_msi_icontrol_task_execution_assessments[control_name] )
    RETURN
        IF (
            ISINSCOPE ( vw_dim_msi_icontrol_controls[control_name] ),
            MAXX (
                FILTER (
                    ALLSELECTED ( vw_fct_msi_icontrol_task_execution_assessments ),
                    vw_fct_msi_icontrol_task_execution_assessments[organization_name] = org
                        && vw_fct_msi_icontrol_task_execution_assessments[control_name] = ctrl
                        && NOT ISBLANK ( vw_fct_msi_icontrol_task_execution_assessments[assessed_on] )
                            && NOT ISBLANK ( vw_fct_msi_icontrol_task_execution_assessments[result_rating] )
                ),
                vw_fct_msi_icontrol_task_execution_assessments[assessed_on]
            ),
            BLANK ()
        )
  • Anonymous's avatar
    Anonymous
    Not applicable

    Worked it out by remove line 

     && NOT ISBLANK ( vw_fct_msi_icontrol_task_execution_assessments[result_rating] )

    from the measur e

    _m_get_max_assessed_on_scope_ctrl

     gives the expected result.