Re: Filtering data with DAX?

Chorri21 · ‎05-18-2017

Hy,

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.

v-shex-msft · ‎05-18-2017

Hi @Chorri21,

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.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Chorri21 · ‎05-18-2017

@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.