Creating yes/no type dimension with measure condition in sql

[rohantaori1996]

I have a location dimension and a inventory measure.

I want to create a yesno type dimension which removes a set of locations and shows inventory greater than 0.

I tried to create a dimension as follows

  dimension: loc_inv_filter {
    label: "Loc ID VWH and inv greater than 0"
    type: yesno
    sql:${d_org_loc_lu.loc_id_desc} NOT IN ('Loc 1', 'Loc 2', 'Loc 3', 'Loc 4') AND ${inventory} > 0;;
  }

But it seems like we cannot use the measure of aggregation type in a dimension. What is the work around for this. I saw an article for dimensionalizing a measure but it is too complicated. Is there any other way?

[Chris_Tinnon]

Your SQL needs to be an IF statement. Not knowing your database, I’ll just do generic sql.

dimension: loc_inv_filter {

    label: "Loc ID VWH and Inv Greater Than 0"

    type: yesno

    sql: IF((${d_org_loc_lu.loc_id_desc} NOT IN ('Loc 1', 'Loc 2', 'Loc 3', 'Loc 4')) AND ${inventory} > 0, 1, 0);;

}