topic Re: Help with filtering table based on filter string in DAX Commands and Tips

Help with filtering table based on filter string

LauritzPBI_O — Mon, 22 May 2023 13:32:49 GMT

I hope someone can help with this problem.

So I'm trying to create a DAX measure, that needs to filter some account numbers in a table based on a string.
The string contains both ranges and specific account numbers, and looks like this:
01011..01090|01901..01902|01904..01959|01980..01998

I can then fetch that value in my measure with this variable:

VAR FilterString =

CALCULATE(

SELECTEDVALUE( 'Account Schedule'[Totaling]),

'Account Schedule'[Row No_] = "01090"

)

However, how do i then translate that string into a useable filter?

Re: Help with filtering table based on filter string

tamerj1 — Mon, 22 May 2023 13:55:55 GMT

Hi LauritzPBI_O

would you please clarify a bit more. Which column is which. Would be great if you could provide some sample data along with the expected result.

Re: Help with filtering table based on filter string

LauritzPBI_O — Tue, 23 May 2023 07:21:58 GMT

Hey thanks for looking into it!
I couldn't seem to attach a file here, so I've made the simplified dataset as a table here.
So ideally I need to sum the values in the "Amount" column, but only for the accounts in "AccountNo" column, that has to be filtered by the filterstring.

AccountNo	Amount
01001	100
01005	100
01010	100
01020	100
01021	100
01022	100
01023	100
01024	100
01025	100
01026	100
01027	100
01028	100
01029	100
01030	100
01031	100
01032	100
01033	100
01034	100
01035	100
01037	100
01039	100
01080	100
01085	100
01090	100
01100	100
01105	100
01110	100
01115	100

Re: Help with filtering table based on filter string

tamerj1 — Tue, 23 May 2023 17:18:26 GMT

LauritzPBI_O

You may upload to any cloud or file transfer service and share the download link.

Re: Help with filtering table based on filter string

LauritzPBI_O — Thu, 25 May 2023 08:34:49 GMT

Ahh my bad!

Here's a link for the example file, its a simple Excel file with two columns

https://www.dropbox.com/scl/fi/asyxrlaf1kr6tfgu9kkzp/Example_Accounts.xlsx?dl=0&rlkey=oqnjelwsff4pr1iq7o8uc8qou

Re: Help with filtering table based on filter string

tamerj1 — Thu, 25 May 2023 10:02:32 GMT

LauritzPBI_O
Apologies, perhaps I did not clarify my question. The sample data placed in your previous reply was just fine. However, the other table that contains the combination string was (and still) missing.

I'm trying to match between your written description of the requirement and the measure/column that you have provided in order to completely understand what exactly do you have and what exactly are trying to accomplish.

The measure/column that you have provided in you original post says the following

VAR FilterString =

CALCULATE(

SELECTEDVALUE( 'Account Schedule'[Totaling]),

'Account Schedule'[Row No_] = "01090"

)

Now the sample data that you have provided contains two columns that are not part of you measure's DAX code!

All that is requirement from your side is provide a clear description of your available inputs and the required output(s). Therefore I was asking for a sample PBIX file that contains samples of all involved tables and relationships (if any) along with measures/calculated columns and one hand written example that explains what are you trying to achieve.