Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Serdet
Post Patron
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

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.

View solution in original post

6 REPLIES 6
jppv20
Solution Sage
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.

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 @Serdet ,

 

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

jppv20
Solution Sage
Solution Sage

Hi @Serdet ,

 

Could you provide some example data and expected output?

 

Hi,

 

Please see the requested below.

 

Distance MilesStart StreetEnd Street
51st street2nd street
61st streetwaterloo road
82nd streetwaterloo road
3waterloo road3rd street
6510th street8th 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 @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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.