Forum Discussion
Measure IF statement for each row
Hi! New to PowerBI and need some help. My understanding basic understanding of columns Vs measures is that columns are set when created and measures allow for more flexability for filtering. Which is why I'm trying to accomplish this with a measure.
I have two tables both with only text fields. The first Table1 contains all possible options and tagging, the second Table 2 is a user input field that allows selections of choices. I want to be able to map the selections from Table2 onto Table1 for a comparision.
My current thought is to create a measure for Table1 that will put a "1" if that value was selected (present in Table2 when filtered by user) and "0" if not selected.
My current DAX function looks like this:
I know the issue is that this function wants a single number output. I don't know how to fix it though. I've tried borrowing the solution from Calcule similarity between two item but I think that this solution relays on the yes/no field already present that I do not have.
hi Croshay ,
The reason your current DAX measure is returning an error is due to how measures handle row context compared to calculated columns.Because you are writing a measure rather than a calculated column, DAX does not automatically know which specific row to evaluate for Table1[feature].To use a column reference inside a measure, you must wrap it in an aggregation function (like MAX or MIN) or use a function like SELECTEDVALUE to reduce it to a single value for the current filter context.Here is how you can fix your exact DAX formula by referencing SELECTEDVALUE:clientSelectionValues = VAR clientChosen = CONCATENATEX(VALUES(Table2[company]), Table2[company], ",") VAR clientFeatures = CONCATENATEX(FILTER(Table2, Table2[company] = clientChosen), Table2[feature], ",") VAR CurrentFeature = SELECTEDVALUE(Table1[feature]) RETURN IF( CONTAINSSTRING(clientFeatures, CurrentFeature), 1, 0 )If this doesn't solve your problem, then can you try this one.
clientSelectionValues_Optimized = // 1. Get the single feature currently being evaluated in Table1 (e.g., in your Matrix/Table visual row) VAR CurrentFeature = SELECTEDVALUE(Table1[feature]) // 2. Create a virtual list of all features currently selected in Table2 VAR SelectedFeatures = VALUES(Table2[feature]) RETURN // 3. Check if the Table1 feature exists in the list of Table2 selections IF( CurrentFeature IN SelectedFeatures, 1, 0 )If this solve your problem, please mark this as solved and give me a kudos.
Thanks
3 Replies
hi Croshay ,
The reason your current DAX measure is returning an error is due to how measures handle row context compared to calculated columns.Because you are writing a measure rather than a calculated column, DAX does not automatically know which specific row to evaluate for Table1[feature].To use a column reference inside a measure, you must wrap it in an aggregation function (like MAX or MIN) or use a function like SELECTEDVALUE to reduce it to a single value for the current filter context.Here is how you can fix your exact DAX formula by referencing SELECTEDVALUE:clientSelectionValues = VAR clientChosen = CONCATENATEX(VALUES(Table2[company]), Table2[company], ",") VAR clientFeatures = CONCATENATEX(FILTER(Table2, Table2[company] = clientChosen), Table2[feature], ",") VAR CurrentFeature = SELECTEDVALUE(Table1[feature]) RETURN IF( CONTAINSSTRING(clientFeatures, CurrentFeature), 1, 0 )If this doesn't solve your problem, then can you try this one.
clientSelectionValues_Optimized = // 1. Get the single feature currently being evaluated in Table1 (e.g., in your Matrix/Table visual row) VAR CurrentFeature = SELECTEDVALUE(Table1[feature]) // 2. Create a virtual list of all features currently selected in Table2 VAR SelectedFeatures = VALUES(Table2[feature]) RETURN // 3. Check if the Table1 feature exists in the list of Table2 selections IF( CurrentFeature IN SelectedFeatures, 1, 0 )If this solve your problem, please mark this as solved and give me a kudos.
Thanks
