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