measure to count rows based on column information

[Matt22365]

Hi

I hope someone can help

I have a dataset (which I cannot alter its format) which I am pulling into BI

What I need is a measure to allow a card visual to count multiple materials in a product

Using the example data below, I want:

• 1 card to show the number of Products with Material 1 (this one I can do).
• A 2nd card which will show the number of Products which contain both Materials 1 & 2

I tried pivoting the data on the material column, however this disrupts my ability to use the materials column as a slicer to add or remove materials from a matrix visual on the same page

Thanks for your help

Matt 

Example data:

Product	Material
Product 1	Material 1
Product 2	Material 1
Product 3	Material 2
Product 1	Material 2

[johnt75 · Accepted solution]

You could try

Materials 1 & 2 =
VAR mat1 =
    CALCULATETABLE ( VALUES ( 'Table'[Product] ), 'Table'[Material] = "Material 1" )
VAR mat2 =
    CALCULATETABLE ( VALUES ( 'Table'[Product] ), 'Table'[Material] = "Material 2" )
RETURN
    COUNTROWS ( INTERSECT ( mat1, mat2 ) )

[Matt22365]

Amazing!, that works perfectly

Thanks for your help

Matt