Count only values that exist in both lists

[SamiS]

Hi,

I have two lists: "ProductsManufactured" and "QualityInspections". Both have a column named 'SerialNumber'.

I want to create a measure that counts manufactured products that have went trough quality inspection during manufacturing process. In the other words, I want to count "ProductsManufactured"-rows whose 'SerialNumber' also exist in "QualityInspection" list.

How I should approach this?

[AlB · Accepted solution]

Hi @SamiS 

I understand by 'lists' you mean tables?  Try this:

Measure =
COUNTROWS (
    INTERSECT (
        DISTINCT ( ProductsManufactured[SerialNumber] ),
        DISTINCT ( QualityInspections[SerialNumber] )
    )
)

"ProductsManufactured" and "QualityInspections". Both have a column named 'SerialNumber'

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[amitchandak]

@SamiS ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.