Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 .
Solved! Go to Solution.
@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.
@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
@Anonymous - Not entirely sure of the ask here, LOOKUPVALUE maybe?
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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 .
@Anonymous
Can you share some sample data and the expected result to have a clear understanding of your question?
You can save your files in OneDrive, Google Drive, or any other cloud sharing platforms and share the link here.
____________________________________
How to paste sample data with your question?
How to get your questions answered quickly?
_____________________________________
Did I answer your question? Mark this post as a solution, this will help others!.
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Fowmy Sharind the sample data as the compelte data set is big with many fields
I want to plot the visuals on how many VMs are missing which softwares .Also some other graphs on subscriptin ID and other criterias based on data set .
1. Distinct VM is coming from a different table as that has the complete inventory of the VMs as there could be some VM missing in User table as well ideally which have no Software installed at all.
2. Distinct softwares I am taking from the user Table itself for finding all permutation and combinations . (as User table only shows the Installed sotware not the missing ones)
3. We can create a new table as well like the output table or if the details can be added on the user tabel itself that would also work as it will help in plotting other graphs.
Please suggest on how can I achieve this , tried with many ways however unfortunately couldnot succeed
Distinct VM Table | Distinct Software Table | User Table | |||||
VM Name | Sofware Names | VM Name | Software Installed | Ower | Subscription ID | ||
VM 1 | Software 1 | VM 1 | Software 2 | a | a@xyz.com | x | |
VM 2 | Software 2 | VM 2 | Software 1 | b | b@xyz.com | y | |
VM 3 | Software 3 | VM 3 | Software 1 | c | c@xyz.com | z | |
VM 4 | Software 4 | VM 3 | Software 2 | c | c@xyz.com | z | |
VM 3 | Software 3 | c | c@xyz.com | z |
Desired Output
VM Name | Sofware Names | Owner | Subscription ID | Status | |
VM 1 | Software 1 | a | a@xyz.com | x | Missing |
VM 1 | Software 2 | a | a@xyz.com | x | Installed |
VM 1 | Software 3 | a | a@xyz.com | x | Missing |
VM 1 | Software 4 | a | a@xyz.com | x | Missing |
VM 2 | Software 1 | b | b@xyz.com | y | Installed |
VM 2 | Software 2 | b | b@xyz.com | y | Missing |
VM 2 | Software 3 | b | b@xyz.com | y | Missing |
VM 2 | Software 4 | b | b@xyz.com | y | Missing |
VM 3 | Software 1 | c | c@xyz.com | z | Installed |
VM 3 | Software 2 | c | c@xyz.com | z | Installed |
VM 3 | Software 3 | c | c@xyz.com | z | Installed |
VM 3 | Software 4 | c | c@xyz.com | z | Missing |
VM 4 | Software 1 | NA | NA | NA | Missing |
VM 4 | Software 2 | NA | NA | NA | Missing |
VM 4 | Software 3 | NA | NA | NA | Missing |
VM 4 | Software 4 | NA | NA | NA | Missing |
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
21 | |
20 | |
15 | |
13 |
User | Count |
---|---|
45 | |
41 | |
39 | |
19 | |
19 |