Forum Discussion
Exclude blanks when using the GENERATESERIES function
Is there a way to exclude blanks when using the GENERATESERIES function? This is the code for the measure that I have written so far. There are blanks in the Table1[pathcolumn] so I am hoping for a way to write the formula to skip over the blanks when using the GENERATESERIES function. I'm trying to see what sections of the _aspath are found in another table and then return those values that are found.
Returned Lookup =
var _aspath = SUBSTITUTE(SELECTEDVALUE(Table1[pathcolumn]), "," , "|" )
var _keypositions =
GENERATESERIES(1, PATHLENGTH(_aspath))
var _lookupvalues =
SELECTCOLUMNS(
_keypositions,
"Value",
LOOKUPVALUE(
Table2[returnedvalue],
Table2[returnedvalue],
PATHITEM(_aspath,[Value])))
var _result =
CONCATENATEX(_lookupvalues, [Value],UNICHAR(10))
RETURN
_result
Hi Neiners,
Thank you for reaching out to the Microsoft Fabric Community Forum.
I’ve taken the time to reproduce your scenario using sample data based on your description. I was able to achieve the expected output:- MAC addresses separated by line feeds (UNICHAR(10)),
- Skipping blank PathColumn values (to avoid GENERATESERIES errors),
- Returning multiple matching values from Table2 using FILTER instead of LOOKUPVALUE.
The measure works as expected and correctly returns MAC address matches and related values from another table, even when multiple matches exist.
For your reference, I’ve attached a .pbix file demonstrating the complete setup with:
- Sample data in Table1 and Table2
- The working DAX measure
- A visual showing the output
Please feel free to explore and adapt it to your actual dataset.
If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.
10 Replies
You can use
Returned Lookup = VAR _aspath = SUBSTITUTE ( SELECTEDVALUE ( Table1[pathcolumn] ), ",", "|" ) VAR _keypositions = GENERATESERIES ( 1, PATHLENGTH ( _aspath ) ) VAR _lookupvalues = SELECTCOLUMNS ( _keypositions, "Value", LOOKUPVALUE ( Table2[returnedvalue], Table2[returnedvalue], PATHITEM ( _aspath, [Value] ) ) ) VAR _removedblanks = FILTER ( _lookupvalues, NOT ISBLANK ( [Value] ) ) VAR _result = CONCATENATEX ( _removedblanks, [Value], UNICHAR ( 10 ) ) RETURN _resultIt still provided me with the GenerateSeries cannot have blank values error. I was able to modify the code to remove the generateseries errors. Now, I need to modify the code a little more to be able to return multiple values from Table 2. The Table2[returnedvalue] column could be found on multiple rows so when trying to return Table2[OtherColumn], multiple rows are found and so it is throwing an error due to using LOOKUPVALUE.
Returned Lookup =var _aspath =IF(ISBLANK(SELECTEDVALUE(Table1[PathColumn])), "0",IF(NOT(ISBLANK(SELECTEDVALUE(Table1[PathColumn]))),SUBSTITUTE(SELECTEDVALUE(Table1[PathColumn]), UNICHAR(10), "|")))var _keypositions =GENERATESERIES(1, PATHLENGTH(_aspath))var _lookupvalues =SELECTCOLUMNS(_keypositions,"Value",LOOKUPVALUE(Table2[OtherColumn],Table2[returnedvalue],PATHITEM(_aspath,[Value])))var _result =CONCATENATEX(_lookupvalues, [Value],UNICHAR(10))RETURN_resultProviding some test data that mimics yours would help us provide a solution that best meets your requirements. That said, I came up with the below dummy data for testing purposes.
Table1
PathColumn Animals
Mammals
Dogs
PoodleAnimals
Mammals
Dogs
LabAnimals
Mammals
Cats
AlleyAnimals
Mammals
Cats
FurryTable2
returnedvalue OtherColumn Animals A Animals B Mammals C Mammals D Dogs E Poodle F Lab G Alley H Furry I Furry J Despite its name, I'm assuming that since you are using Table2[returnedvalue] in the Search_ColumnName argument of LOOKUPVALUE, you are looking to get the concatenation of Table2[OtherColumn] related by Table1[PathColumn] path items --> Table2[returnedvalue] --> Table2[OtherColumn]. Example, for Animals|Mammals|Cats|Alley, we should get : A|B|C|D|H, or in other words:
We can do this by moving away from LOOKUPVALUE and instead actively getting all related OtherColumn values by using TREATAS between Table1 path items (still using your pipe substitution + generate series with path length method) and Table2[returnedvalue]:
Returned Lookup = IF( HASONEVALUE( Table1[PathColumn] ), VAR _txt = VALUES( Table1[PathColumn] ) VAR _txtPath = SUBSTITUTE( _txt, UNICHAR( 10 ), "|" ) VAR _txtPAthLen = PATHLENGTH( _txtPath ) VAR _pathIndexes = GENERATESERIES( 1, _txtPathLen, 1 ) VAR _pathIndexesLookups = GENERATE( _pathIndexes, CALCULATETABLE( VALUES( Table2[OtherColumn] ), TREATAS( { PATHITEM( _txtPAth, [Value], TEXT ) }, Table2[returnedvalue] ) ) ) RETURN CONCATENATEX( _pathIndexesLookups, Table2[OtherColumn], UNICHAR( 10 ) ) )Output:
Hi Neiners ,
Did the reply offered help you solve the problem, if it helps, you can consider to accept it as a solution so that more user can refer to, or if you have other problems, you can offer some information so that can provide more suggestion for you.
Best regards,
Lucy Chen
Hi Neiners,
Thank you for reaching out to the Microsoft Fabric Community Forum.
I’ve taken the time to reproduce your scenario using sample data based on your description. I was able to achieve the expected output:- MAC addresses separated by line feeds (UNICHAR(10)),
- Skipping blank PathColumn values (to avoid GENERATESERIES errors),
- Returning multiple matching values from Table2 using FILTER instead of LOOKUPVALUE.
The measure works as expected and correctly returns MAC address matches and related values from another table, even when multiple matches exist.
For your reference, I’ve attached a .pbix file demonstrating the complete setup with:
- Sample data in Table1 and Table2
- The working DAX measure
- A visual showing the output
Please feel free to explore and adapt it to your actual dataset.
If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.Hi Neiners,
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please accept it as a solution and give it a 'Kudos' so other community members with similar problems can find a solution faster.
Thank you.
Hi Neiners,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.Hi Neiners,
May I ask if you have resolved this issue? If so, please mark it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
