I have this really funky database in webi and I'm trying to overwrite a dimension to the value of a record with a common dimension. (i can do this line-by line, but i can't seem to get totals this way) .three variables - facility ID (common dimension), amount (measure), risk code (dimension to overwrite).
the way my database is setup is that there can be multiple records for a single facility ID, but if the amountfield has a value, the risk code field comes up "NA" (not NULL), if amount is 0 then there's a value in the risk code field. i need these these NA's to become whatever the risk code is for the matching facility ID that isn't NA. if any of that makes sense.
i made a custom variable that sets NA values to 0 then gets the max Risk Code
=Max(If([Risk Code]="NA";"0";[Risk Code]))
the max function turns my dimenion into a measure, but the dimension is actually numerical values anyway so doesn't really matter. it works great when i have a table with each Facility ID line item, but if i try to make a table that sums the amounts of each risk code, i get a multivalue error.
Assuming i need to add some kind of In([Facility ID]) to my formula, but when i do that, it doesn't work. Could i use a UseMerged function or something like that? I'm completely lost. Any help would be greatly appreciated. Thanks!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.