Search code examples
powerbidaxpowerquerydata-analysispowerbi-desktop

DAX to check if a value matches a value from another table

In PowerBi desktop I have two different tables with different data that share 1 column. The tables contain information about products and each table has a column titled "SKU ID". I want to create a measure that counts how many products have a matching SKU ID from a different table. I hope that makes sense. Below are my tables:

Table 1 Table 2

So I just want a measure that counts how many Product Names have a SKU ID that matches a Truck Route with the same SKU ID. I've been stuck for days! Thank you all in advance!

UPDATE Tables in text format are as follows:

Product Name    |    SKU ID
------------------------------
Toy                  182772
Boat                 294773
Car                  928844
Cup                  277453
Plate                933111
Chair                182772
Table                182772
Remote               277453
Fork                 933111


Truck Route  |  SKU ID
--------------------------
MEM1              182772
SDF3              294773
DTW1              928844
SAN8              277453
MIA8              933111
MIA8              182772
SAN8              182772
MEM1              277453
SDF3              933111

** I am looking for a result where if you click on the table in PowerBi on route MEM1 - 3. So when you click on MEM1, the count of matching SKU IDs is returned from the Product name table.

Solution

  • Your data looks very unusual to me so it is hard to know precisely what the output should look like. Normally, you would achieve this in PBI using relationships but as I said your data is hard to understand without more context. Is the following what you want:

    enter image description here

    If so, add a table and add the truck route and SKU ID fields making sure they're set to Don't Summarise.

    enter image description here

    Add a measure as follows:

    Measure = COUNTROWS(FILTER(Products, Products[SKU ID] = SELECTEDVALUE('Truck Routes'[SKU ID])))

    Drag the measure into the table as the 3rd column.