cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Post Patron

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)

Can this be done in DAX? Visaul level filtering is not an option.
1 ACCEPTED SOLUTION
Community Support

Hi @Serdet

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 6
Solution Sage

Hi @Serdet ,

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.
Post Patron

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.

Solution Sage

Hi @Serdet ,

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

Solution Sage

Hi @Serdet ,

Could you provide some example data and expected output?

Post Patron

Hi,

 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

Community Support

Hi @Serdet

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.

Announcements

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors