cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jonnokc Regular Visitor
Regular Visitor

totaling values that contain

Hello,

 

I need help mimicking a task I used to do in access. In access you can easily group and filter using the "Like" function, but Power Bi does not use this function and will not import queries that do. So I need to recreate my queries in Power Bi.

 

How can I use Power Bi to filter for all rows containg a specific text value.

 

For example.... Column (a) contains names, column (b) contains job titles.

 

I want to filter looking at column B for all rows that contains "Manager" & "HR".

 

thank you

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: totaling values that contain

DAX supports both FIND and SEARCH, the major difference being that SEARCH supports wildcard characters:

https://support.office.com/en-US/article/SEARCH-Function-DAX-b26334b2-7831-4fce-aad0-68b0510612a5

 

 

M (Power Query language) has Text.Contains but that does not support wildcard characters.

https://msdn.microsoft.com/en-us/library/mt253324.aspx

 

 

In DAX you could combine the two searches with a couple IF statements to get the effect you are looking for.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Super User
Super User

Re: totaling values that contain

Well, this could be done a number of different ways. The simplest way would be to do something like the following:

 

1. Create a query to your data source that includes all your columns and rows. This will create a table in your data model or potentially multiple tables if you have several sources.

2. In your data model (Excel spreadsheet looking icon) go to the table that has your "Job_Title" column (right-hand side lists tables), then click on your "Modeling" tab and then click "New Column" in the "Calculations" area of the ribbon

3. Assuming that your column is called "Job_Title", put in the following formula:

 

HRManager = IF(IFERROR(SEARCH("Manager",[Job_Title]),0),IFERROR(SEARCH("HR",[Job_Title]),0),0)

 

This creates a column "HRManager" that is not 0 if BOTH "HR" and "Manager" are found in "Job_Title".

 

Then, all you have to do is filter your visualization so that you do not include rows where "HRManager" is 0.

 

Again, this is just one method that is simple to show as an explanation. There are other ways to do similar things. I am not 100% certain of what you are trying to ultimately accomplish and I do not have your exact data, so you may have to change some things around depending upon your exact circumstances. 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


4 REPLIES 4
Super User
Super User

Re: totaling values that contain

DAX supports both FIND and SEARCH, the major difference being that SEARCH supports wildcard characters:

https://support.office.com/en-US/article/SEARCH-Function-DAX-b26334b2-7831-4fce-aad0-68b0510612a5

 

 

M (Power Query language) has Text.Contains but that does not support wildcard characters.

https://msdn.microsoft.com/en-us/library/mt253324.aspx

 

 

In DAX you could combine the two searches with a couple IF statements to get the effect you are looking for.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Jonnokc Regular Visitor
Regular Visitor

Re: totaling values that contain

Hello Smoupre Thank you for your reply. I am afraid I am very new to this. Is it possible for you to take this down a technical level for me?

 

Am I creating a measure for this or am I going into the query editor and cloning my table and somehow placing this formula?

 

How would I write this.

 

Table = Table1

column name = Job_Title

 

Filtering for all rows that contain "Manager"

 

Once again thank you for your help.

 

 

Super User
Super User

Re: totaling values that contain

Well, this could be done a number of different ways. The simplest way would be to do something like the following:

 

1. Create a query to your data source that includes all your columns and rows. This will create a table in your data model or potentially multiple tables if you have several sources.

2. In your data model (Excel spreadsheet looking icon) go to the table that has your "Job_Title" column (right-hand side lists tables), then click on your "Modeling" tab and then click "New Column" in the "Calculations" area of the ribbon

3. Assuming that your column is called "Job_Title", put in the following formula:

 

HRManager = IF(IFERROR(SEARCH("Manager",[Job_Title]),0),IFERROR(SEARCH("HR",[Job_Title]),0),0)

 

This creates a column "HRManager" that is not 0 if BOTH "HR" and "Manager" are found in "Job_Title".

 

Then, all you have to do is filter your visualization so that you do not include rows where "HRManager" is 0.

 

Again, this is just one method that is simple to show as an explanation. There are other ways to do similar things. I am not 100% certain of what you are trying to ultimately accomplish and I do not have your exact data, so you may have to change some things around depending upon your exact circumstances. 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Jonnokc Regular Visitor
Regular Visitor

Re: totaling values that contain

Amazing.

 

Thank you very much