Drill in Measures with CASE statements

Hello! I am currently having an issue fixing a little drill down bug at work.

I currently have a measure in my view that calculates whether two dimensions are greater than the other and in another clause, whether that dimension is greater than 0. It currently works and calculates correctly however the drill downs are not bringing up the how that number was calculated. Is there any way to implement that functionality in the dimension below?

I understand that for “filters” to carry over to drill downs they must be in LookML filters however I have a CASE statement that needs to carry over.

measure: count_pga_met {
type: sum
sql: CASE WHEN (${TABLE}.pga_earned >= ${TABLE}.pga_req) AND (${TABLE}.pga_req > 0) THEN 1 ELSE 0 END ;;
drill_fields: [detail*]
}

Note: pga_earned and pga_req were measures but are now dimensions of the table.

 measure: pga_new {
type: sum
sql: ${TABLE}.pga_new ;;
label: "PGA New"
drill_fields: [detail*]
}

  measure: pga_recovery {
    type: sum
    sql: ${TABLE}.pga_recovery ;;
    label: "PGA Recovery"
    drill_fields: [detail*]
  }

  dimension: pga_req {
    type: number
    sql: ${TABLE}.pga_req ;;
    label: "PGA Required"
    drill_fields: [detail*]
  }

Thank you so much for the time + hasty replies. Thankful for this community.

Cheers

Kevin

@paola wrote up a wee explanation of this over here:

In a nutshell: Case statements don’t pass along to drills, but filters: parameters defined in the LookML do, so using filters will make your drills match up with your expected results.

1 Like

Izzy,

Thanks so much for getting back to me. Yes, I have seen (and been studying her post) for some time now.

I have currently done this for some of the CASE statements we have because they’re relatively simple and don’t involve multiple dimensions:

  measure: count_total_req {
    type: count
    # sql: CASE WHEN (${TABLE}.total_msr_req > 0) THEN 1 ELSE 0 END ;;
    filters: {
      field: total_msr_req
      value: ">0"
    }
    drill_fields: [detail*]
  }

As you can see the filter key takes place of the previous sql key. But what do I do when the CASE statement in the SQL isn’t as a simple and involves another dimension? :

 measure: count_total_met {
    type: sum
    sql: CASE WHEN (${TABLE}.total_msr_earned >= ${TABLE}.total_msr_req) AND (${TABLE}.total_msr_req > 0) THEN 1 ELSE 0 END ;;
    drill_fields: [detail*]
  }

Thank you!

Team,

I have figured out how to implement the functionality needed.

In order to maintain the CASE statement in your drill down, you must create a dimension that calculates the CASE and filter on that dimension. As below:

     dimension: greater_than_msr_req {
    type: yesno
    case: {
      when: {
        sql: ${total_msr_earned} >= ${total_msr_req} ;;
        label: "yes" # returns the label if true (type string)
      }
    }
    hidden: yes
  }

  measure: count_total_met {
    type: count
    # The filters below are implementing the functionality of the SQL below
    # CASE WHEN (${TABLE}.total_msr_earned >= ${TABLE}.total_msr_req) AND (${TABLE}.total_msr_req > 0) THEN 1 ELSE 0 END 
    filters: {
      field: total_msr_req
      value: ">0"
    }
    filters: {
      field: greater_than_msr_req
      value: "'yes'" # filter the drill to only the values with a 'yes' string
    }
    drill_fields: [detail*]
  }

Thank you again @izzy for making me think a little harder :muscle:t5:

Cheers,

Kevin D.

1 Like

Ah, thanks for sharing the final solution Kevin— Looks perfect.

1 Like