Exclude blanks when using the GENERATESERIES function

[Neiners]

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

[v-ssriganesh]

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.

[v-ssriganesh]

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.

[v-ssriganesh · Accepted solution]

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.

[v-ssriganesh]

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.

[Anonymous]

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

[johnt75]

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
    _result

[Neiners]

It 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

_result

[MarkLaf]

Providing 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 Poodle

Animals Mammals Dogs Lab

Animals Mammals Cats Alley

Animals Mammals Cats Furry

Table2

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:

[Neiners]

I have multiple tables that contain assets and properties of those assets. One of the properties is the MAC Address(es) of an asset. For each asset, the MAC Address(es) that may be associated with it are seperated by a line feed UNICHAR(10). What I would like to do is search for each MAC Address that is assigned to an asset (SUBSTITUTE) function and see if I can find any of those assigned MAC Addresses appear in any of the other tables that may have MAC Address(es) assigned to those assets and return different measures. One measure would return the matching MAC Address(es) and another measure would return another column (example: IP address) from the other table. Sometimes though assets won't have a MAC Address assigned to them so I need to overlook those in the GENERATESERIES.

[Neiners]

I am still getting the generateseries function cannote be blank. The problem is The PathColumn may be blank so I am attempting to exclude the generateseries function if the that column is blank.