Forum Discussion
Composite weight using values from 2 tables.
Somewhat complex challenge you got there. Try this measure, I am not sure I got all your conditions right, if not let me know and I'll rework:
CompositeWeight = VAR AWeight = SUM(TableA[Weight]) VAR ACountry = MAX(TableA[Country]) VAR Prod = MAX(TableA[Product]) VAR NoResponsesCountry = COUNTROWS(FILTER(TableB, TableB[Product] = Prod && TableB[Response Recieved?] = "Y" && ACountry = TableB[Country])) VAR NoResponses = COUNTROWS(FILTER(TableB, TableB[Response Recieved?] = "Y" && ACountry = TableB[Country])) VAR Props = DIVIDE(NoResponsesCountry, NoResponses) VAR X = LEFT(MAX(TableA[Product]), 1) = "X" RETURN IF(X, DIVIDE(AWeight, Props),1)I am assuming here that you would use this in a visual with at least Product and Country as row contexts. I'm using a lot of variables in order to explain, no need for all of that in your work product of course.
erik_tarnvik wrote:It is this portion:
... VAR X = LEFT(MAX(TableA[Product]), 1) = "X" RETURN IF(X, DIVIDE(AWeight, Props),1)you could rewrite this as
IF(LEFT(MAX(TableA[Product]), 1) = "X", DIVIDE(AWeight, Props),1)The IF function evalutes wether the first letter of TableA[Product] is equal to "X" and returns the ratio if it is and 1 otherwise. The LEFT function requires a scalar value and not a column, that is why I use the MAX function to extract a scalar value. As I commented, this measure assumes that it is evaluated in a filter context where TableA[Product] only has one value. So, you could have used MIN just as well as MAX as the result is the same for a column with only one value.
thanks Erik. my question was meant for Ashish. I have now edited my post. noob mistake.
erik_tarnvik wrote:No problem. BTW the answer is that Ashish_Mathur filtered the visual on products:
ah, got it !
well that kind-of circumvents the original problem as i need the measure to be used in futher calculations...