Count with if and concat

[Daniel77]

Hi All,

I have the following If statement:

if(not wildmatch(concat(StandardTypeGrouped),'*2*')

, 'No environmental')

This returns the correct values. I now need to Count the returned values in a KPI. I tried like this:

if(not wildmatch(concat(StandardTypeGrouped),'*2*')

, count(distinct StandardTypeGrouped))

But i get a null.

Then i tried to wrap in a count but this causes nested aggregation.

Can anyone please assist with achieving the count of the original expression?

i need a count of the StandardTypeGroup where not matching the value 2. Sounds so simple 😞

Thank you so much.

Daniel

[BrunPierre]

Try

=Count(DISTINCT Aggr(if(not WildMatch(Concat(StandardTypeGrouped),'*2*'), StandardTypeGrouped), StandardTypeGrouped))

[Daniel77]

Hi There BrunPierre,

Thank you so much for the reply, I only just got to look at this. I am getting a number now but it is returning '1' and not the required number. Do you have any other idea on this? thanks again for taking the time to look at this. 

Regard,

Daniel

[marcus_sommer · Accepted solution]

I assume the applied aggr-dimension isn't suitable else there may be the object-dimensions listed and/or the dimensions in which the concat() should be calculated, something in this way:

sum(Aggr(if(not WildMatch(Concat(StandardTypeGrouped),'*2*'), 1), Dim1, Dim2))

whereby the bold-marked are just place-holder.

[Daniel77]

Thank you Marcus,

I have used your measure and added a single Aggr dimension and i am getting the correct numbers now.

Thank you @BrunPierre also for your input on this. Really appreciate your time to help me resolve this.

Thansk

Daniel