SUM, Filter by Two Columns text values

[Anonymous]

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)

Can this be done in DAX? Visaul level filtering is not an option.

[jppv20]

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")

Jori

If I answered your question, please mark it as a solution to help other members find it more quickly.

[Anonymous]

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.

[jppv20]

Hi @Anonymous ,

I don't think I completely understand what you mean. Could you explain a bit more what's nog working correctly? 

[jppv20]

Hi @Anonymous ,

Could you provide some example data and expected output?

[Anonymous]

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

[v-chenwuz-msft · Accepted solution]

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.