Forum Discussion
SUM, Filter by Two Columns text values
Hi All,
I am trying to perform a calculation where I do the following
SUM column 1
Filter by column 2 (multiple text values)
and
Filter by column 3 (multiple text values)
Hi Anonymous
Try this measure:
Measure = VAR _filter_from_Start = FILTER( 'Table', [Start Street] = "1st street" || [Start Street] = "2nd street" || [Start Street] = "10th street" ) VAR _filter_from_End = FILTER( _filter_from_Start, [End Street] = "2nd street" || [End Street] = "3rd street" || [End Street] = "8th street" ) RETURN SUMX( _filter_from_End, [Distance Miles] )But the conditions is too long or in your data, is that possible to use start street not equal waterloo road?
If is , filter with the condition
[Start Street]<>”waterloo road”Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
Hi Anonymous ,
Could you provide some example data and expected output?
Hi,
Please see the requested below.
Distance Miles Start Street End Street 5 1st street 2nd street 6 1st street waterloo road 8 2nd street waterloo road 3 waterloo road 3rd street 65 10th street 8th street SUM of column 'Distance Miles;
Filter column 'Start Street' to = 1st street, 2nd street, 10th street
Filter column 'End Street' to = 2nd street, 3rd street, 8th street
Expexted outcome = 70
Hope this helps
Hi Anonymous
Try this measure:
Measure = VAR _filter_from_Start = FILTER( 'Table', [Start Street] = "1st street" || [Start Street] = "2nd street" || [Start Street] = "10th street" ) VAR _filter_from_End = FILTER( _filter_from_Start, [End Street] = "2nd street" || [End Street] = "3rd street" || [End Street] = "8th street" ) RETURN SUMX( _filter_from_End, [Distance Miles] )But the conditions is too long or in your data, is that possible to use start street not equal waterloo road?
If is , filter with the condition
[Start Street]<>”waterloo road”Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous ,
You can create this measure:
MilesDisctance = CALCULATE(SUM('Table'[Distance Miles]),'Table'[Start Street]="1st street"||'Table'[Start Street]="2nd street"||'Table'[Start Street]="10th street",'Table'[End Street]="2nd street"||'Table'[End Street]="3rd street"||'Table'[End Street]="8th street")JoriIf I answered your question, please mark it as a solution to help other members find it more quickly.Thanks. I have tried this measure but it seems to be doing the following
SUM of mileage for column 'Start Street' with text filters + SUM of mileage for column 'End Street' with text filters.
Instead it should be doing SUM of mileage for columns 'Start Street' and 'End Street' where both columns meet the text filter criteria.
Hi Anonymous ,
I don't think I completely understand what you mean. Could you explain a bit more what's nog working correctly?
