cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
spuder
Resolver IV
Resolver IV

DAX: Combine CALCULATE and CONTAINS

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. 

1 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')

 

1.PNG

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)

 

2.PNG3.PNG

 

 

 

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" )

4.PNG

 

 

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

 

 

Untitled.png

 

 

Hope it helps

Konstantinos Ioannou

View solution in original post

5 REPLIES 5
spuder
Resolver IV
Resolver IV

@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. 

@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')

 

1.PNG

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)

 

2.PNG3.PNG

 

 

 

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" )

4.PNG

 

 

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

 

 

Untitled.png

 

 

Hope it helps

Konstantinos Ioannou

View solution in original post

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
Community Champion
Community Champion

@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
Memorable Member
Memorable Member

@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/

Konstantinos Ioannou

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors