Forum Discussion
Anonymous
2 years agoNot applicable
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 ()
)
- Anonymous2 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
- AnonymousNot 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 () ) - AnonymousNot 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.