Create a Column with range From-To comma separated using DAX

[denisedf]

Hello everyone,

I have FROM/TO columns with the starting and ending numbers from a range and I am wondering if there is an easy way in DAX to create a column with the range values comma separated as shown below:

TIA,

Denise

[v-deddai1-msft]

Hi @denisedf ,

Are you trying to use range instead of single-selection for the slicer, you can try to modify your measure to:

check =
VAR minpicked =
    MIN ( NUMBER_SLICER[Number] )
VAR maxpicked =
    MAX ( NUMBER_SLICER[Number] ) //assigns the number selected from slicer to a var
RETURN
    IF (
        ISFILTERED ( NUMBER_SLICER[Number] ),
        "Y",
        // if no NUMBER is picked from slicer everything = Y
        IF (
            OR (
                AND (
                    //these two lines below define that NUMBER selected from slicer needs to be in both PSN FROM/TO "AND" CCL FROM/TO
                    MAX ( VALUES_VIEW[FROMPSN] ) <= minpicked
                        && MAX ( VALUES_VIEW[TOPSN] ) >= maxpicked,
                    //PSN from/to filtering
                    MAX ( VALUES_VIEW[CCL_FROM] ) <= minpicked
                        && MAX ( VALUES_VIEW[CCL_TO] ) >= maxpicked //CCL from/to filtering
                        && //and M_NUMBER must contain 500T8 in name
                        SEARCH (
                            "500T8",
                            MAX ( VALUES_VIEW[M_NUMBER] ),
                            ,
                            0
                        ) <> 0
                ),
                //line below is the "OR" (where NUMBER from slicer is only in FROMPSN/TOPSN and M_NUMBER should not have 500T8 in name
                (
                    MAX ( VALUES_VIEW[FROMPSN] ) <= minpicked
                        && MAX ( VALUES_VIEW[TOPSN] ) >= maxpicked
                        && //M_NUMBER does not contain 500T8 in name
                        SEARCH (
                            "500T8",
                            MAX ( VALUES_VIEW[M_NUMBER] ),
                            ,
                            0
                        ) = 0
                )
            ),
            "Y" //this is the Y
            ,
            "N" //else --if not in range set it to N
        )
    )

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[denisedf · Accepted solution]

Hi I tried your solution but I was getting an error message that I could not have more than one record per line or something.

I was able to create all the values what I needed using this:

check from/to tails =
var alltails = CONCATENATEX(VALUES(TAILS_POWERBI),TAILS_POWERBI[Tails],",") //this is a table that lists all the values and I concatenate them comma separated
var startPosition = SEARCH(MAX(MODSUM_VIEW[FROMPSN]),alltails) //here I define which is the from value position
var endPosition = (SEARCH(MAX(MODSUM_VIEW[TOPSN]),alltails))+5 //here I define which is the to value position (+5 because my values are always 5 digits long)
var fromto_range = mid(alltails,startPosition,endPosition-startPosition) (here I build the values from/to based on the positions
return fromto_range

This is what it looks like:

[denisedf]

@v-deddai1-msft No, users will not select ranges, they will select one or many single numbers and the task is to filter and display only th eones that are in the FROM/TO range.

[v-deddai1-msft]

Hi @denisedf ,

Would you please try the following measure:

check =
VAR picked =
    VALUES ( NUMBER_SLICER[Number] ) //assigns the number selected from slicer to a var
RETURN
    IF (
        ISBLANK ( picked ),
        "Y",
        // if no NUMBER is picked from slicer everything = Y
        IF (
            OR (
                AND (
                    //these two lines below define that NUMBER selected from slicer needs to be in both PSN FROM/TO "AND" CCL FROM/TO
                    COUNTROWS (
                        INTERSECT (
                            GENERATESERIES ( MAX ( VALUES_VIEW[FROMPSN] ), MAX ( VALUES_VIEW[TOPSN] ), 1 ),
                            picked
                        )
                    ) > 0,
                    //PSN from/to filtering
                    COUNTROWS (
                        INTERSECT (
                            GENERATESERIES ( MAX ( VALUES_VIEW[CCL_FROM] ), MAX ( VALUES_VIEW[CCL_TO] ), 1 ),
                            picked
                        )
                    ) > 0
                        && //and M_NUMBER must contain 500T8 in name
                        SEARCH (
                            "500T8",
                            MAX ( VALUES_VIEW[M_NUMBER] ),
                            ,
                            0
                        ) <> 0
                ),
                //line below is the "OR" (where NUMBER from slicer is only in FROMPSN/TOPSN and M_NUMBER should not have 500T8 in name
                (
                    COUNTROWS (
                        INTERSECT (
                            GENERATESERIES ( MAX ( VALUES_VIEW[FROMPSN] ), MAX ( VALUES_VIEW[TOPSN] ), 1 ),
                            picked
                        )
                    ) > 0
                        && //M_NUMBER does not contain 500T8 in name
                        SEARCH (
                            "500T8",
                            MAX ( VALUES_VIEW[M_NUMBER] ),
                            ,
                            0
                        ) = 0
                )
            ),
            "Y" //this is the Y
            ,
            "N" //else --if not in range set it to N
        )
    )

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[vanessafvg]

it would probably be easier to create something like this in power query, you could create a function that passes the from and to values and it could create a list.

https://docs.microsoft.com/en-us/powerquery-m/list-numbers

Did you want to do this in dax particularly.  What are you wanting to do with the values once you have them, just display them?

[denisedf]

Hi @vanessafvg 

I will have a slicer where the user will do multiple selections and these selections will check if values are in the range and filter rows from the table based on the results.

I already do that for one single record selected the slicer, where I do a "between" calculation (shown below) which sets Y and N to filter the records in table VALUES_VIEW.

The curveball is that with the multiple selection in the slicer, the "between" >= =< will not work (or would require a lot of work) and I thought of tweeking the logic below to use  IN (New column where values will be comma separated) 

check =
var picked = VALUE(SELECTEDVALUE(NUMBER_SLICER[Number])) //assigns the number selected from slicer to a var
return
if(isblank(picked),"Y", // if no NUMBER is picked from slicer everything = Y
IF (
OR(
AND( //these two lines below define that NUMBER selected from slicer needs to be in both PSN FROM/TO "AND" CCL FROM/TO
MAX(VALUES_VIEW[FROMPSN]) <= picked && MAX(VALUES_VIEW[TOPSN]) >= picked, //PSN from/to filtering
MAX(VALUES_VIEW[CCL_FROM]) <= picked && MAX(VALUES_VIEW[CCL_TO]) >= picked //CCL from/to filtering
&&
//and M_NUMBER must contain 500T8 in name
SEARCH("500T8",MAX(VALUES_VIEW[M_NUMBER]),,0)<>0
),
//line below is the "OR" (where NUMBER from slicer is only in FROMPSN/TOPSN and M_NUMBER should not have 500T8 in name
(MAX(VALUES_VIEW[FROMPSN]) <= picked && MAX(VALUES_VIEW[TOPSN]) >= picked &&
//M_NUMBER does not contain 500T8 in name
SEARCH("500T8",MAX(VALUES_VIEW[M_NUMBER]),,0)=0)
)
,"Y" //this is the Y
,"N" //else --if not in range set it to N

))

Any help is appreciated. Does not necessarily need to be DAX, power query would be fine too.

Denise

[Ashish_Mathur]

Hi,

If you want filters and slicers to work, then all numbers should be in one column with each number in its own row.  This can be done easily in the Query Editor.  Would you be interested in that solution?

[denisedf]

@Ashish_Mathur The problem of having each number in one column in its own row is that I have currently 430000+ rows of data to which, many of these rows the From/to is From = 55001 and To = 59999. 
I would easily end up with 2 billion rows.

[Ashish_Mathur]

Hi,

But unless you do that, you will not be able to select individual numbers via filter/slicers.