DAX: Combine CALCULATE and CONTAINS

[spuder]

Hi Folks,

this is my first article/question here in this forum. Hopefully I'm right.

I'm using power bi desktop and have the following problem. I have a sales table that shows turnovers of different product types.

e.g. 

iphone 4s

iphone 5

samsung galaxy s6

samsung galaxy s7

microsoft lumia 950

microsoft lumia 640

Now I wanted to calculate the turnover for every brand. Therefore I tried to use the Calculate DAX Formula, but it seems to be impossible to use a placeholder when filtering by text. Is this right or do I make something wrong.

Brand Revenue = CALCULATE(SUM('turnover'[sales price]; 'turnover' [product name] = ''%iphone%'')

The second thing I tried was to filter by the CONTAIN Function.

Brand Revenue = CALCULATE(SUM('turnover'[sales price]; CONTAINS('turnover';'turnover' [product name];"iphone"))

But with that I got an error message that it's not allowed to do so.

The background is that I want to compare and calculate the product turnover to the total turnover.

e.g. iphones turnover is 30% of total. 

For any help thanks in advance.

PS: Right now I'm not so much familiar with DAX formula. But I'm always willing to learn. 

[spuder]

@konstantinos I read the text in the first link. And it sounds like a good solution to my problem. Unfortunately it doen't work when I enter the formula. The problem seems to be that I am not smart enough unable to use the SEARCH Function in the right way. I started very simple. Just with test = SEARCH("iphone"; *and here I am not able to choose a column from my table.*) Power BI just shows me calculated measures but no column from the original table. Maybe you know what my mistake is?

@Sean Thanks for your suggestion. Unfortunately it is not that easy. The problem is that this is a kind of free text column. So it could happen that someone enters iphone 4s and another one 4s iphone and the third one apple iphone 4s. So create the column before is not really easier. 

[konstantinos · Accepted solution]

@spuder You know it is not about smartness 🙂 

Let's  go to your solution. 

You cannot use search() like that. You need to write the measure as in site ( approximately ) and also have a parameter table.

There is also a way to do it in powerquery but is Case sensitive ( iPhone is different than iphone ) so better with DAX ( data model not case sensitive )

This is the data I enetred for demo ( table name is 'Sales')

Instead of hard coding values is better to add a table with the brands so you can add or remove more later.

First "Enter Data" with the brands, table name is 'Brands'  ( if you are using excel create a table an import it as linked table or better with powerquery from table)

Create a calculated column in 'Sales' tab and enter the formula 

The formula also returns 'Other' in case you it doesn't find a match

Phone Brand =

VAR searchbrand =
    FIRSTNONBLANK (
        FILTER (
            VALUES ( Brands[Brand] );
            SEARCH ( Brands[Brand]; Sales[Model]; 1; 0 )
        );
        1
    )

RETURN
    IF ( NOT ( ISBLANK ( searchbrand ) ); searchbrand; "Other" )

Now you can use 'Phone Brand" as rows , slicer whatever

Hope it helps

[spuder]

Oh yes @konstantinos that was the missing piece of the puzzle. (using calc. column instead of measure)

Thank you so much. It is working even better than expected.

In my pracitcal case (which had nothing to do with smartphones) I just wanted to extract one type of categorie. With this formula I am able to extract them all. Perfect.

For me now it is time to dive deeper into dax. Seems to be a powerful weapon.

[Sean]

@spuder you are missing a ) and a ,

Brand Revenue = CALCULATE ( SUM(turnover[sales price]), turnover[product name]="iphone 4s" || turnover[product name]="iphone 5")

This will sum all iphone 4s and iphone 5 - however you probably have 6, 6s, 6se etc...

So Create a Column => Product Brand and/or Product Type say Apple and iPhone, iPad, etc..

Then you can just use Product Brand and/or Product Type in the above formula instead of having to list all products and/or Types

So then you sum Apple, iPhone && || ipad etc...

iPhone Revenue = CALCULATE ( SUM(turnover[sales price]), turnover[Product Brand]="Apple", turnover[Product Type]="iPhone")

[konstantinos]

@spuder There is no DAX function for it yet..But @powerpivotpro   has a great article exactly for this

So create a calculated column to serve as Brand and then use it in reports

http://www.powerpivotpro.com/2014/01/containsx-revisited-what-was-the-match/

Probably there is a way to do it in query mode before you load the data but not sure if I read it. Check Ken Plus blog, should have something similar http://www.excelguru.ca/blog/