Custom Measure based on logical OR of 4 dimensions in table

[Chris_Young]

I am working with a table which has 4 related dimensions (let’s say A,B,C,D) any of which may be not null. Now I understand how to make a custom measure based on one of these. e.g.

measure: foo_A {
type: count
filters: [A: “foo”]
}

and I see that you can count based on multiple dimensions:

measure: foo_ABCD {
type: count
filters: [A: “foo”, B: “foo”, C: “foo”, 😧 “foo”]
}

but this would count only records in which A, B, C and D are all set to ‘foo’. What I would like is a foo_count that would count the records in which A,B,C or D are set to ‘foo’. Any suggestions on how to do that in LookML?

[george_woodhead]

An alternative way you could achieve this is by first creating a dimension using a CASE WHEN in the sql parameter which will return the id or whatever you’re counting. You can then use a measure to count that dimension. For example:

dimension: foo_ABCD {
    type: string
    sql: CASE WHEN ${A} = "foo" OR ${B} = "foo" OR ${C} = "foo" OR ${D} = "foo" THEN ${id} END ;;
  }

measure: count_foo_ABCD {
    type: count_distinct
    sql: {foo_ABCD} ;;
}

[Chris_Young]

Thanks for the response. This is in fact very similar to what I ended up doing. Using the dimension work-a-round seems the accepted practice for now.