Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 ...
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    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.

  • Anonymous's avatar
    Anonymous
    6 years ago

    Greg_Deckler Thanks .It worked 🙂 

     

     

    Can we also do this in same table (29B) instead of a new table creation . 

  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    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.

Fowmy's avatar
Fowmy
Icon for Super User rankSuper User
6 years ago

Anonymous 

Create a New Table using the code below, which will include all combinations

New 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 🙂

YouTube  LinkedIn

 


 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      Fowmy
      Icon for Super User rankSuper User
      6 years ago

      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 following

      Missing = 
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 🙂

      YouTube  LinkedIn

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Fowmy  I tried but that is not adding a new column as there is a Many to Many cardinality setup .

         

        VN name can be duplicate in both the tables . 

         

        For Eg  User table has additional columns like  User name, Email ID , Company timestamp etc . I want to add these fields in New table created for ploting the visuals  Or is there any other method to use these directly in visuals.

        Graphs like for list of VMs these software are missing and Owner details  .