Can you have an if = statement applied to a dimension (PO number) over multiple rows?

[davyqliks]

in my load script i have an if statement that defines if a po number is   'We Care' or 'Not Sustainable'

the dimension is named as [We Care].

A PO number can have both values on separate so i can have:

po1234 'We Care'

po1234 'Not Sustainable'

i have to write an if statement in the table to state if We Care for a single po has both values the result is We Care

however, i cannot associate my effort below to the field PO number

=If([We Care] = 'We Care' and 'Not Sustainable' , 'We Care','Not Sustainable')

Please see the below table and the mentioned measure on the right cell which should show as 'We Care' in this case

i hope someone can assist with this... i have been going round in circles.

Thank you

Daniel

[marcus_sommer · Accepted solution]

The syntax isn't quite correct - instead of:

=If([We Care] = 'We Care' and 'Not Sustainable' , 'We Care','Not Sustainable')

it needs to be:

=If([We Care] = 'We Care' and [We Care] = 'Not Sustainable' , 'We Care','Not Sustainable')

Beside this the if-check will be performed on a row-level. This means [We Care] could always have a single value and therefore your AND check must fail. To apply evaluations on a column-level you need to apply an aggregation. In your case it may look like:

if(aggr(count(distinct [We Care]), YourRelevantDimensions) = 2, 'then', 'that')

- Marcus

[davyqliks]

Thank you so much,

that's so helpful and good to know.

i wonder if you can assist with one more thing.

When i write this in a measure i get the desired result :

///////////

IF( Match(Fibre,'Polyester GRS') AND

([Share of fabric in the style (percentage) 1]/100* [Share of sustainable quality in the fibre(percentage) 1]/100 * Percentage/100) > .10, 'y', 'N')

/////////////

Note Polyester GRS y and Modal N when bot >.1

When i add to the load script the fibre is not taken into consideration:

and i get a Y and N for  both fibres

the tables are linked.... i just cannot work out why the same does not work from a measure to a load.

Sorry to be cheeky and jump on this thread but you were my only reply (hope). 

thanks in advance Marcus

[marcus_sommer]

Within the UI you could apply measures which relates to fields from different tables - if the tables are properly linked you will get the correct results. Within the script all fields respectively values for a calculation must come from a single table - if they aren't there you need some additionally (mapping/join) steps in beforehand to add them there.

- Marcus

[davyqliks]

Thanks Marcus, makes sense.

Regards,

Daniel