Forum Discussion
Exclude blanks when using the GENERATESERIES function
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.
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
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.
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
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:
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.
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.
