I've spend a lot of time trying to figure it out how to resolve my problem with no success. I have two data source (one much large than the other): the first one (excel file) has departments and store as dimensions plus one measure called Evaluation (which is binary), the second table (a larger sql query) has department, store and a date field as dimensions with no measure. Obviously the dimensions with the same name are linked. Firstly I need to create a calculated field that meet the following three cases: a) IF Evaluation = 0 then "out", b) IF Evaluation = 1 and COUNT(rows in second table) = 0 THEN "error" and c) IF Evaluation = 1 and COUNT(rows in second table) > 0 THEN "ok". The field should be plot over deparment and store. I couldn't resolve how to blend data from table 1 and 2, because it throw a error message of "cannot mix aggregate and non-aggregate arguments". I feel a little confused, I could do this in excel in a blink. Secondly I need the filter the COUNT(rows in second table) given the last X days from a parameter bar.
The extract file is attached.
thank you beforehand
Would you be able to re-post your workbook, making sure to save it as a twbx file?