Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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.
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.
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.
Amazing.
Thank you very much
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |