DAX: how to use contains

[Anonymous]

Hello I have a measure with a calculated sum that I use in a visual. I set some 'contains' filters on the visual. However, I can choose only 2 text strings that it can contain:

So if I want to filter on 3 or more text strings this is not possible. I think it is best to solve this in the measure, but how to do this?

Thank you in advance!

[v-jiascu-msft · Accepted solution]

Hi @Anonymous ,

If you'd like to select more then two values, you can use the basic filtering. Please refer to the snapshot below.

If you'd like to use a measure, you can try the "in" function.

Measure =
CALCULATE (
    SUM ( 'tabel_Transactions'[Value] );
    ( 'Category'[Category] = "Actual" );
    FILTER (
        'Datumtabel';
        ( 'Datumtabel'[MonthNr] >= 1
            && 'Datumtabel'[MonthNr] <= 'Month'[Maand Value] )
    );
    FILTER (
        ALL ( Account[Account_Path] );
        Account[Account_Path] IN { "RE-2110"; "SE-2110"; "SE-2120" }
    )
)

Best Regards,

[Anonymous]

@AnonymousIs it a requirement to do it at the visual level filters only? Why dont you create a formula using DAX and there you can test the condition as many times as you want?

https://docs.microsoft.com/en-us/dax/contains-function-dax

[Anonymous]

@Anonymous  that was indeed my question, how to include in a dax measure. But I do not know how to do this also after reading the article. I want to do a calculted sum when it contains a certain text string.

I have this measure:

CALCULATE (SUM('tabel_Transactions'[Value]); ('Category'[Category]="Actual"); FILTER ( 'Datumtabel'; ( 'Datumtabel'[MonthNr] >= 1 && 'Datumtabel'[MonthNr] <= 'Month'[Maand Value] ) ) )/-1000000 

And I want to calculated the sum only when: 

[Account_Path] = "RE-2110"  or

[Account_Path] = "SE-2110"  or

[Account_Path] = "SE-2120" 

I come this far, but is shows no values and not sure how to add the other 2 account path I want to filter on

CALCULATE (SUM('tabel_Transactions'[Value]); ('Category'[Category]="Actual"); FILTER ( 'Datumtabel'; ( 'Datumtabel'[MonthNr] >= 1 && 'Datumtabel'[MonthNr] <= 'Month'[Maand Value] ));FILTER (ALL(Account[Account_Path]);CONTAINS(Account;Account[Account_Path];"RE-2110")))

[v-jiascu-msft · Accepted solution]

Hi @Anonymous ,

If you'd like to select more then two values, you can use the basic filtering. Please refer to the snapshot below.

If you'd like to use a measure, you can try the "in" function.

Measure =
CALCULATE (
    SUM ( 'tabel_Transactions'[Value] );
    ( 'Category'[Category] = "Actual" );
    FILTER (
        'Datumtabel';
        ( 'Datumtabel'[MonthNr] >= 1
            && 'Datumtabel'[MonthNr] <= 'Month'[Maand Value] )
    );
    FILTER (
        ALL ( Account[Account_Path] );
        Account[Account_Path] IN { "RE-2110"; "SE-2110"; "SE-2120" }
    )
)

Best Regards,

[Anonymous]

@v-jiascu-msft  maybe you know how to solve the following.

This measure is working fine;  

= CALCULATE (SUM('tabel_Transactions'[Value]); ('Category'[Category]="Actual"); FILTER ( 'Datumtabel'; ( 'Datumtabel'[MonthNr] >= 1 && 'Datumtabel'[MonthNr] <= 'Month'[Maand Value] ));OR(PATHCONTAINS(Account[Account_Path];"RO-2130");PATHCONTAINS(Account[Account_Path];"SO-2230")||PATHCONTAINS(Account[Account_Path];"EC-3240")))/-1000000

But I have another path that I want to include ProfitCenter[ProfitCenter_Path];"MB_003"

So it should we this profitcenter AND one of the 4 accounts mentioned in the measure above.

I tried the AND function but I cannot get it working. Any idea?

[v-jiascu-msft · Accepted solution]

Hi @Anonymous ,

It could be like this one. But it's from another table, did they connect properly?

Measure =
CALCULATE (
    SUM ( 'tabel_Transactions'[Value] );
    ( 'Category'[Category] = "Actual" );
    FILTER (
        'Datumtabel';
        ( 'Datumtabel'[MonthNr] >= 1
            && 'Datumtabel'[MonthNr] <= 'Month'[Maand Value] )
    );
    AND (
        OR (
            PATHCONTAINS ( Account[Account_Path]; "RO-2130" );
            PATHCONTAINS ( Account[Account_Path]; "SO-2230" )
                || PATHCONTAINS ( Account[Account_Path]; "EC-3240" )
        );
        PATHCONTAINS ( ProfitCenter[ProfitCenter_path]; "MB_003" )
    )
) / -1000000

Best Regards,

[Anonymous]

@v-jiascu-msft  thanks. I tried that also, but gives a blank result. So I will have a look into the relationship between the tables today. I will come back when that is the solution (or not).

[Anonymous]

@v-jiascu-msft  thanks. this will work, however I do not need to filter on the exact string, but if the total text string contains the substring. 

This measure seems to work...: 

CALCULATE (SUM('tabel_Transactions'[Value]); ('Category'[Category]="Actual"); FILTER ( 'Datumtabel'; ( 'Datumtabel'[MonthNr] >= 1 && 'Datumtabel'[MonthNr] <= 'Month'[Maand Value] ));OR(PATHCONTAINS(Account[Account_Path];"RE-2110");PATHCONTAINS(Account[Account_Path];"SE-2210")))

and within the OR function I can use the || to add 3rd or 4rd pathcontains.