Use dimension when creating measure with Case

miguelposada · 03-15-2022 03:21 AM

I am trying to create a measure conditionally based on a dimension.

column is a dimension while geo_area.count_anon and count_anon are measures of sum type.

This measure is not working well.

  measure:measure_count_lvl3 {
    type: number
    sql:
      CASE
      WHEN  ${column}='GEO_AREA_ID' THEN ${geo_area.count_anon}
      ELSE ${count_anon}
    END ;;
  }

The error:

What can I do to solve this?

Desi1

Hey Miguel!
The issue is coming as you are using a type number. Type number usually should be used to do a calculation.
In your case I suggest you to change the measures used in the CASE when statement with the dimensions that you’ve used in those measures. Like this:

 
 measure: geo_area.count_anon {
    type: sum
    sql:${dimension_1} ;;
  }

 measure: count_anon {
    type: sum
    sql:${dimension_2} ;;
  }

 measure:measure_count_lvl3 {
    type: sum
    sql:
      CASE
      WHEN  ${column}='GEO_AREA_ID' THEN ${dimension_1}
      ELSE ${dimension_2}
    END ;;
  }

Hope that helps you!

Dawid

${column} is not in your query / data table,hence your problem. I’m assuming you don’t want it but want just one measure.

You would actually need this logic in the sum measure:

measure: some_sum {
  type: sum
  sql: IF(${column} = "GEO_AREA_ID", ${count_column_a}, ${count_column_b}) ;;
}

What this would do, it will make sure that ${column} is within an aggregation so it will be evaluated on row level and there’s no more need for grouping.

miguelposada

Hey Miguel!
The issue is coming as you are using a type number. Type number usually should be used to do a calculation.
In your case I suggest you to change the measures used in the CASE when statement with the dimensions that you’ve used in those measures. Like this:

 
 measure: geo_area.count_anon {
    type: sum
    sql:${dimension_1} ;;
  }

 measure: count_anon {
    type: sum
    sql:${dimension_2} ;;
  }

 measure:measure_count_lvl3 {
    type: sum
    sql:
      CASE
      WHEN  ${column}='GEO_AREA_ID' THEN ${dimension_1}
      ELSE ${dimension_2}
    END ;;
  }

Hope that helps you!

I’ve been trying that and finally it works puting type sum in measure_count_lvl3 but I have to put a type number in the dimension 1 and 2.

This problem appears but it seems to work anyway.

miguelposada

Hey Miguel!
The issue is coming as you are using a type number. Type number usually should be used to do a calculation.
In your case I suggest you to change the measures used in the CASE when statement with the dimensions that you’ve used in those measures. Like this:

 
 measure: geo_area.count_anon {
    type: sum
    sql:${dimension_1} ;;
  }

 measure: count_anon {
    type: sum
    sql:${dimension_2} ;;
  }

 measure:measure_count_lvl3 {
    type: sum
    sql:
      CASE
      WHEN  ${column}='GEO_AREA_ID' THEN ${dimension_1}
      ELSE ${dimension_2}
    END ;;
  }

Hope that helps you!

Well, actually I’ve working on it and still giving me mistakes. How can I call a sth from another view? Am I explaining myself?

Dawid

you need to reference the view name ${view_name.field}

miguelposada

you need to reference the view name ${view_name.field}

But now I have this error

Desi1

Hey Miguel!
Can you share the definition of the other measures and dimensions that you have now?

Dawid

Then I think you’re back to the original problem. You have a measure of type number with SUM and a dimension , you expect it to get aggregated but it won’t. Anythin with type:number, be it a dimension or measure, isn’t going to be treated as an aggregation: it doesn’t get wrapped in SUM(), COUNT() or anything like that

miguelposada

Hey Miguel!
Can you share the definition of the other measures and dimensions that you have now?

Yes sure

geo_area view:

  measure: count_anon {
    type: sum
    sql: ${TABLE}.count_anon ;;
  }

lvl3 view (Which is the view Im working on):

  dimension: column {
    type: string
    sql: ${TABLE}.column ;;
  }

  measure: count_anon {
    type: sum   
    sql: ${TABLE}.count_anon ;;
  }

  measure:measure_count_lvl3 {
    type: sum
    sql:
      CASE
      WHEN  ${column}='GEO_AREA_ID' THEN ${geo_area.count_anon}
      ELSE ${count_anon}
    END ;;
  }

I also tried the next measure in case the one before but I have the same mistake

 measure:measure_count_lvl3_IF {
    type: sum
    sql: IF(${column}='GEO_AREA_ID',${geo_area.count_anon},${count_anon}) ;;
  }

miguelposada

My objective is to show the geo_area.count_anon column when column is filter by GEO_AREA_ID or show the count_anon when it isnt

Desi1

The definition of the dimensions and the measure should be like this:

dimension: dim_1{
  type: number
}

dimension: dim_2{
  type: number
}

 measure:measure_count_lvl3 {
    type: sum
    sql:
      CASE
      WHEN  ${column}='GEO_AREA_ID' THEN ${dim_1}
      ELSE ${dim_2}
    END ;;
  }

You don’t need to create additional measures to build the measure measure_count_lvl3

Dawid

Have a try doing it this way:

# Geo Area

dimension: count_anon_dim {
    type: number
    sql: ${TABLE}.count_anon ;;
}

# LVL3 View

dimension: count_anon_dim {
    type: number
    sql: ${TABLE}.count_anon ;;
}

measure: measure_count_lvl3 {
    type: sum
    sql: IF(${column} = 'GEO_AREA_ID', ${geo_area.count_anon_dim}, ${count_anon_dim}) ;;
}

miguelposada

Thank you,

Now there is no mistake but I shouldnt be 0, I dont know what is the problem now.

Anyways, thanks for everything