Forum Discussion
Solved
Create a Column with range From-To comma separated using DAX
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 ...
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:
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
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:
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: