How to compare two column with string values?

[Parvane_Zand]

Hi everyone.
I’ve imported Insight data to eazyBI to be able to compare two different columns (host name & server name). They are actually two attributes of object Volume in insight.
Is there a way to compare these two columns?
I tried using calculated measure:
IIF(
[Measures].[Volume Host] = [Measures].[Volume Server Host],
“Equal”,
“Not Equal”
)
but it does not work with strings. Also tried CASE WHEN, with the same result! (error for strings)

[Elita.Kalane]

Hi @Parvane_Zand,
Welcome to eazyBI community!
​
​It seems you were missing the function CoalesceEmpty, which helps determine what to return if the field is empty. Try the formula below and see if it returns the expected results
​

​IIF(
CoalesceEmpty([Measures].[Object Volume Host],"") MATCHES 
CoalesceEmpty([Measures].[Object Volume Server Host],""),
"Equal",
"Not Equal"
)

​
​Best wishes,

Elita from support@eazybi.com

[Mark_Z]

could this function be used in the post I published below? I tried using same function but it did not come out as planned.

Help with filtering two columns by same text

I need help filtering between 2 columns in my eazybi report. It seems simple but i cant get correct results. with the below measure it gives me True for the columns with “Diamond”. However, I want the measure to be marked True even with a column showing “Diamond,Triangle,Square”. With this example is shows false as containing “Diamond”. Assume because the string includes text other than “Diamond”. [Measures].[Issue Platform] = “Diamond” OR [Measures].[Issue Affected Products] = “Diamond”

[Elita.Kalane]

Hi @Mark_Z

You can defining a new calculated measure with formula below :

CoalesceEmpty([Measures].[Issue Platform],"") matches ".*Diamond.*" OR
CoalesceEmpty([Measures].[Issue Affected Products],"") matches ".*Diamond.*"

The symbols between “Diamond” will allow you to search for the records containing “Diamond”

Best wishes,

Elita from support@eazybi.com

[Mark_Z]

this doesnt appear to work for me. It shows False in the columns even when there are known matches between the text. Do i need to have certain formatting enabled?

[Elita.Kalane]

Hi @Mark_Z

Apologies, it seems I had added an extra/unnecessary space before “Diamond”. Can you try now with the below formula?

CoalesceEmpty([Measures].[Issue Platform],"") matches ".*Diamond.*" OR
CoalesceEmpty([Measures].[Issue Affected Products],"") matches ".*Diamond.*"

Best wishes,

Elita from support@eazyBI.com

[Mark_Z]

@Elita.Kalane

CoalesceEmpty([Measures].[Issue Platform],"") matches ".*Diamond.*" OR
CoalesceEmpty([Measures].[Issue Affected Products],"") matches ".*Diamond.*"

awesome, this worked perfectly! thanks again Elita.

[Mark_Z]

Hi Elita,

the calculated measure worked but when i choose to filter by True which shows there is a match i get the following error. Any idea how to get around this?

[Elita.Kalane]

Hi @Mark_Z

I assume you used the equal sign to filter the “true” values? Since this is a string value, try defining the filter with “matches.”

This should return the “true” values then:

Best wishes,

Elita from support@eazybi.com