Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Jonnokc
Frequent 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
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

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. 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.

 

 

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. 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Amazing.

 

Thank you very much

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.