Forum Discussion
Lookup table with matching value
I have a dataset with 3 tables . 2 Master table with Unique records and one user table
| VM Name |
| VM 1 |
| VM 2 |
| VM 3 |
| VM 4 |
| Sofware Names |
| Software 1 |
| Software 2 |
| Software 3 |
| Software 4 |
| Uswer Table | |
| VM Name | Software Installed |
| VM 1 | Software 2 |
| VM 2 | Software 1 |
| VM 3 | Software 1 |
| VM 3 | Software 2 |
| VM 3 | Software 3 |
Can we identify and add new columns indication which VM is missing which Software and add it in table as records in table for ploting the visuals. tried with lookup but could no succeed . Please assist .
Anonymous - Probably. See attached PBIX below sig, Tables (29), (29a), (29b), (29c). 29c is the output.
Table (29c) = ADDCOLUMNS( GENERATE('Table (29)','Table (29a)'), "Owner",LOOKUPVALUE('Table (29b)'[Ower],[VM Name.1],[VM Name],"NA"), "Email",LOOKUPVALUE('Table (29b)'[Email],[VM Name.1],[VM Name],"NA"), "Subscription ID",LOOKUPVALUE('Table (29b)'[Subscription ID],[VM Name.1],[VM Name],"NA"), "Status",IF(COUNTROWS(FILTER('Table (29b)',[VM Name.1]=[VM Name]&&[Software Installed]=[Software Names]))>0,"Installed","Missing") )Turned out to be a lot of LOOKUPVALUE after all.
Greg_Deckler Thanks .It worked 🙂
Can we also do this in same table (29B) instead of a new table creation .
Anonymous - I do not believe so. If you want a full inventory you have to kind of "blowout" the table into more rows than you have in any of the base tables.
12 Replies
- Fowmy
Super User
Anonymous
Create a New Table using the code below, which will include all combinationsNew Table = CROSSJOIN( vm,Software )Add the following Column to it to identify missing combination:
Missing = IF( ISEMPTY( FILTER( user, User[VM Name]=EARLIER('New Table'[VM Name]) && User[Software Installed] = EARLIER('New Table'[Sofware Names]) ) ), "MISSING")________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
Fowmy It Helps .. Can I also pick more columns from User table based on VM name in the new table created ?.
User table has User details for each VM . This would help in plotting the data
- Fowmy
Anonymous
Yes sure,
The 1st code will work without any change.
You can modify the 2nd code which adds the column. You can add more fileds starting with && as I did for Software Installed in the followingMissing = IF( ISEMPTY( FILTER( user, User[VM Name]=EARLIER('New Table'[VM Name]) && User[Software Installed] = EARLIER('New Table'[Sofware Names]) ) ), "MISSING")
Let me know if you face any issue________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
- Greg_Deckler
Community Champion
Anonymous - Not entirely sure of the ask here, LOOKUPVALUE maybe?
- amitchandak
Anonymous , this will give you a new table
except(crossjoin(all(Table1[VM Name]), selectcolumns(Table2,"Software Installed",Table2[Sofware Names])),Table3)
for except you need same name across tables
