SUM Filter Text

[Anonymous]

Hi All,

I am trying to create a measure to sum one column then filter that sum based upon text values in another column.

(The text filter will have to be based on a "or" function, not "and") (This has to be done in DAX as it will be part of another measure further down the line).

So far I have tried

Site Mileage = CALCULATE(SUM(TrakM8[Distance (mi)]), TrakM8[Start Date] = "10th Street", "11th Street", "12th Street", "13th Street", "14th Street") 

But this is not working.

Any ideas on how to resolve this?

Many thanks,

[Samarth_18]

Hi @Anonymous ,

It would be helpful to answer more specifically if you could share some sample data with expected output. Based on what you are trying for that, below would be ideal code:-

Site Mileage =
CALCULATE (
    SUM ( TrakM8[Distance (mi)] ),
    TrakM8[Start Date]
        IN { "10th Street", "11th Street", "12th Street", "13th Street", "14th Street" }
)

Thanks,

Samarth

[Anonymous]

Hi, 

Many thanks for your response.

I copied your DAX word for word and recieved no errors but when placing into into a table or card visual I recieve the below error.

Also how would I ammend the DAX to allow for text filters from two different columns?

Cheers,

Elliot 

[Samarth_18]

Is it possible for you share sample data with expected output or sample file after removing confidencial data??

[Anonymous]

My bad, I have just noticed an stupid area in my DAX.

How can I add another text filter for another column?

Sample data below.

Distance (mi)	Start Date
5	10th Street
10	10th Street
54	10th Street
64	11th Street
24	12th Street

[Samarth_18]

Seems "start date" column contains text but it is datatype marked as date. If you convert that column as text then you will not get that error.

Could you please elaborate below requirement:-

How can I add another text filter for another column?

[Anonymous]

I want to filter based on text for two columns.

For example,  

SUM of miles

Filter by column 1 "text"

Filter by column 2 "text"

Hope that makes sense.

[Samarth_18]

if column 1 and column 2 belongs to same table you can directly use that as visual level filter and you select the value according to your need and it will filter the data.

[Anonymous]

This filter needs to be kept in the DAX as I will be creating additonal DAX using this measure.

The below DAX works fine but is there any way I can add another column text filter to it?

Site Mileage =
CALCULATE (
    SUM ( TrakM8[Distance (mi)] ),
    TrakM8[Start Date]
        IN { "10th Street", "11th Street", "12th Street", "13th Street", "14th Street" }

[Samarth_18 · Accepted solution]

Yes you can add like below

Site Mileage =
CALCULATE (
    SUM ( TrakM8[Distance (mi)] ),
    TrakM8[Start Date]
        IN { "10th Street", "11th Street", "12th Street", "13th Street", "14th Street" },
TrackM8[somecolumn] IN {somevalue1,somevalue2,somevalueN}
)