Delimited data as a filter in a measure

[jch2shill]

Hello everyone,

I have data coming into Power BI from Dynamics Business Central. It is in dot and pipe delimited format. The data could have many iterations of dots and pipes for example:

This is a simplified example but I need to use the values in that column as filters for my measure. For example, my measure to sum the amount of sales, would look like:

Calculate(

Sum(Sales[Amount]),

filter(GL Accounts,GL Accounts[GLNumber]>=”70000” && Accounts[GLNumber]<=”70600” || Accounts[GLNumber]=”67760”

)

                )

My question is do I need to split and list the many combinations of numbers for that column or can I use the column as it is in my measure? I do not want to create a measure (like in my example above) for every possible row from by GL Account table.

Thank you for any direction you can send me.

[jch2shill]

Thank you. That gives me a direction to go in. I will have to figure out how to make it dynamic as the position of the numbers with the dots and pipes is random. That is, in your second line of data above, it could easily be 70000..70600|67760

[rsbin]

@jch2shill ,

The permutations and combinations were my main concern.  Hence, why I only wanted to give direction.
With the variety of text functions available, you can also add a condition to determine where your "|" and ".." are and then determine how to extract the GL Acct number.  Glad you were able to grasp the concept.

Good Luck!

[rsbin]

@jch2shill ,

Not sure if I have an exact solution for you, but I will attempt to show you how I would tackle.
Appears you want to set up your GL Accounts as you have MIN, MAX, and Other.
So in your Measure you would refer to these fields rather than exact Acct #'s. 

To get there, I attempted to use the Split by Delimiter function in Power Query, but didn't quite work out for me.

So, let's do it in DAX.  Started out with a few examples.

Original

65000..67300
67760|70000..70600
86000

Then created 3 new Columns:

MinGL = SWITCH(
           TRUE(),
           AND( CONTAINSSTRING( [Original], ".." )=TRUE(), CONTAINSSTRING( [Original], "|" )=FALSE() ), LEFT( [Original], 5 ),
           AND( CONTAINSSTRING( [Original], "|"), CONTAINSSTRING( [Original], ".." ) ), MID( [Original], 7, 5 ),
           [Original] )

MaxGL = SWITCH(
           TRUE(),
           AND( CONTAINSSTRING( [Original], ".." )=TRUE(), CONTAINSSTRING( [Original], "|" )=FALSE() ), RIGHT( [Original], 5 ),
           AND( CONTAINSSTRING( [Original], "|"), CONTAINSSTRING( [Original], ".." ) ), RIGHT( [Original], 5 ),
           [Original] )

OtherGL = SWITCH(
           TRUE(),
           CONTAINSSTRING( [Original], "|"), LEFT( [Original], 5 ),
           "" )

Original                      MinGL   MaxGL   OtherGL

65000..67300	65000	67300
67760|70000..70600	70000	70600	67760
86000	86000	86000

Using the SWITCH function, you can then add as many permutations and combinations that your dataset requires.

Hope you are able to understand what I am going for and that you can get it to work for you.  Have attached the pbix for your reference.

Regards,