Forum Discussion

Neiners's avatar
Neiners
Helper II
1 year ago
Solved

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...
Need Help
Show and Tell
Tips and Tricks
  • v-ssriganesh's avatar
    v-ssriganesh
    1 year ago

    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.

johnt75's avatar
johnt75
Super User
1 year ago

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's avatar
    Neiners
    Helper II
    1 year ago

    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's avatar
      MarkLaf
      Super User
      1 year ago

      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

      returnedvalueOtherColumn
      AnimalsA
      AnimalsB
      MammalsC
      MammalsD
      DogsE
      PoodleF
      LabG
      AlleyH
      FurryI
      FurryJ

       

      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's avatar
        Neiners
        Helper II
        1 year ago

        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.