Hello PBI community!
I have two tables, Table A is an inventory table with a column for IP address and Network name.
The Table B contains a column for IP address, CVE number and Severity. Table A contains a list of devices in the network. Table B contains a list of CVEs per devices, where 1 device can have multiple CVEs. Not every device in Table A will have a CVE.
I am trying to create a datamodel to use with reports based on data from both tables. My initial approach was to use Table A as Dimension table and Table B as a Fact table and create a One-many Relationship. I also create 2 other dimension tables with CVE and Severity data.
I want to summarize the data in Table B using 3 x Dimension tables of IP address, CVE and Severity.
Not every single device in Table A will have a corresponding record in Table B. In my reports, I filter out any device with out a CVE record in Table B. It seems to work however, when creating measures, I now have to add filters for me to see the correct data (filter out blank CVEs).
My question is should I Merged Table A and Table B using the IP Address as the Primary key to create a new fact Table with only the devices which contain CVEs and then create relationships to the 3 dimension tables?
I hope my question and explanation makes sense, any guidance on this use case or when to use Merge vs Joins and Filters would be great!
Thank you! R
