how do you relate two tables when columns and values don't match

[rachelb123]

I have a table filled with list of products with various categories and another table with tools that these categories can be attributed to. They don't have any columns in common. For example, Categories 1 and 2 will be related to Tool A and Categories 3 will be related to Tool B etc. I'm not sure what the process of going about doing this here is, since the tool table doesn't have any product/category information. 

ToolName	Use Date
Tool A	1/7/25
ToolB	3/7/2025
Tool A	4/4/2025

Product	Category
z	2
x	1
x	1
c	3
v	2
b	3

I want to be able to show coverage like this :

Tool	Product Applicability %
A	4/6
B	2/6

[v-sdhruv]

Hi @rachelb123 ,

Just wanted to check if you got a chance to review the suggestions provided and whether that helped you resolve your query?

Thank You @Cookistador  and @Royel  for providing valuable inputs to the query.

[Royel]

@rachelb123  to achieve this you need to create a mapping table 

Tool Name	Category
Tool A	1
Tool A	2
Tool B	3

Now, Create two one-to-many relationships:

• Bridge table to Products table (on Category)
• Bridge table to Tools table (on Tool Name)
• Set cross-filter direction to "Both" for proper filter propagation, this approach will allow you to create the coverage analysis you're looking for.

Now, Use this DAX measure for your Product Applicability calculation

Product Applicability = 
VAR ProductsForTool = 
    CALCULATE(
        COUNTROWS(Products)
    )
VAR TotalProducts = 
    CALCULATE(
        COUNTROWS(Products),
        ALL(Products)
    )
RETURN
    DIVIDE(ProductsForTool, TotalProducts, 0)

Find this helpful? ✔ Give a Kudo • Mark as Solution – help others too!

[Cookistador · Accepted solution]

Hi @rachelb123 

To be able to achieve this calculation, you need to add a mapping table between your tool and cateogry

Tool Name	Cateogry
Tool A	1
Tool A	2
Tool B	3

Then, you could get what you need with the following measure

Product Applicability =

Var Product = Products for Tool =
CALCULATE(
COUNTROWS(Products)
)

Var TotalProduct =  CALCULATE(
COUNTROWS(Products),
ALL(Products) 
)

Return 

Divide(Product, TotalProduct,0)

If you need more support, do not hesitate to share more details with us

[rachelb123]

Thank you ! It works but I realised there are products that have to be excluded so I need to work on it a little more to figure that out.