Merge vs Relationship

[ryan_telstra]

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

[turbofred69]

Hi,

I've facing the same questions. Can anyone summarize  the Pros and Cons of both approaches:

- Merge in M-query

- Use of a relationship to show values of a table B columns matching  the lines of table A?

Thanks in advance

[v-jayw-msft]

Hi @ryan_telstra ,

If i understand you correctly, you can use Query Editor->Merge Queries as New and Left outer join to combine your tables. Please see the below screenshot:

Measure is also a choice and you can use filter() function or If() function to filter what you want.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.