I am working on a visualisation which calculates the cumulative total by using specific Chart accounts starting with specific values. The dataset are comming from different databases with diffferent data but allways with the same chart account numbers. The thing is that the visualisation will be used across the company and some departments have additional extensions on the chart account number and they need to be included. There is that advanced filtering option and in Query Editor as well, but can I do something with DAX so that I can create a column and filter the data in the correct way, and use it in a way that will work universaly for the chart accounts? The objective is to avoid the manual selection of account numbers because there are to manny of them to do so.
Can you share some detail information about your goal?
>>can I do something with DAX so that I can create a column and filter the data in the correct way, and use it in a way that will work universaly for the chart accounts?
Current power bi not support to create a dynamic calculated column/table based on filter or slicer, you should use measure to instead.
BTW, if you want to filter with dax, please not use "all" or other functions which can broken the current filter.
@v-shex-msft Thank you for your replay! Well, the thing is that we have the general ledger chart account numbers, for example 1001, 1002 (...) and so on which will be used in department A. Then we have that same chart account which has an additional extension of 01, 02 which is then 100101,100102 (...) used only in department B (In their database - the database in department A has not this extension). I need a function similar to SQL LIKE% so that I can filter data which starts with '100%' which will extract the desired chart account numbers so that even if that department has that 'extra' extension in its database it will be included.
I also believe that if I create a column and extract the data in that column by using the querry editor's "starts with" option it won't be included if another user in another department uses that report.
Current power bi not contains any function to achieve fuzzy lookup.
For your requirement, I'd like to suggest you to take a look at Marco Russo's blog: