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?
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} ;;
}
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.