Evaluating one dimension value against another dimension with multiple values


Here’s my problem:

Dimension: Batch Number
Options: 1 to n

Dimension: Ripeness
Options: Good or Spoiled

So if all of the apples are good, then good, if one or more are spoiled say spoiled.

1 - apple - spoiled | Spoiled
1 - apple - good | Spoiled
1 - apple - good | Spoiled

2 - apple - good | Good
2 - apple - good | Good
3 - apple - good | Good

How can I create the dimension that says Spoiled or Good?

Hi @ranranshoe

One way to do this would be to create two measures: one called something like “Spoiled Apples”, which is a count of spoiled apples, and one called “Good Apples”, which is a count of good apples. From there you could set up a look with three columns: Batch number|Spoiled Apples|Good Apples

This would give the number of spoiled and good apples per batch. Then use an if statement in the table calculation to say "if(Spoiled Apples = 0, “Good”, “Spoiled”) - this is essentially saying that if there are no spoiled apples, the batch is good; otherwise the batch is spoiled.

Hope this helps - happy to provide more clarity if needed.

@adstott90 This worked perfectly! Exactly what I was looking for. Thank you so much!