CG Conditional formatting issue wth string

[smpa01]

@AlexisOlson @parry2k @BernatAgulloMVP 

I am using calculation group to scale up my current project. CG is working beautifully except

the conditional formatting with calculation group fails when the measure returns a string but works if if it is a double.

Works when DOUBLE

Fails when STRING, the measure does not even evaluate correctly

What am I doing wrong, how this can be fixed?

I want to be able to use existing calculation group and be able to conditionally format based on STRING.

PFA minimally produced sample of a large data model highlighting only the issue.

Thank you in advance.

[v-jiewu-msft]

Hi @smpa01 ,

Based on the information, try using the following DAX formula.

VAR xx =CALCULATE ( [left_selected], CG[Name] = "diff" ) 

-- output type String -- possible values {'y','n'}
VAR yy = CALCULATE ( [left_selected], CG[Name] = "is_negative" )
VAR yy_numeric = IF ( yy = "n", "n", "y" )

VAR conds =
    SWITCH (
        TRUE (),
        ------------------Works----------------------------
        //currentField = "is_negative" && xx>0,1
        //,currentField = "is_negative" && xx<0,-1
        -----------------DOES NOT WORK ---------------------
        currentField = "is_negative" && yy_numeric = "n", "n",
        currentField = "is_negative" && yy_numeric = "y", "y" 
        //  currentField = "is_negative" && yy_numeric = 1, "n",
        //  currentField = "is_negative" && yy_numeric = -1, "y" 
        ------------------Works-------------------------------
        //currentField = "is_negative" && xx > 0, "n",
        //currentField = "is_negative" && xx < 0, "y"

        ,BLANK()
    )
//VAR debugger = SWITCH ( TRUE (), currentField = "is_negative", xx )
RETURN
    conds

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[smpa01]

@v-jiewu-msft  thank you very much and it worked in the sample provided.

However, can you please explain why it works? Without fully understanding the WHY this can't be utilized in Production. My measures are far more complex than the described in sample.

Therefore, can you please possibly provide me with an explanation of this behavior so that in any similar circumstances, currently known (as the issue described in the question) or unknown I can navigate by myself.

Also, a deeper look into this calculation group shows it is a classic example of sideways recursion (unknowningly landed at it) which is not advised. I am willing to alter the entire calculation item authoring from this way to prevent further slipping into black hole in Production.

Can you please shed some light? I am willing to hear your thoughts.

[v-jiewu-msft]

Hi ,

I think the [left_selected] measure calculates numeric values and cannot be compared directly to strings. So direct comparison has no effect. Using If function to determine the size of the value.

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.