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.
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:
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.