Forum Discussion
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.
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.
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.
Community ChampionWell, 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.
Amazing.
Thank you very much