Forum Discussion

Matt22365's avatar
Matt22365
Icon for Resolver III rankResolver III
4 years ago
Solved

measure to count rows based on column information

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:

ProductMaterial

Product 1

Material 1
Product 2Material 1
Product 3Material 2
Product 1Material 2
Need Help
  • johnt75's avatar
    johnt75
    4 years ago

    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 ) )

2 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      Matt22365
      Icon for Resolver III rankResolver III
      4 years ago

      Amazing!, that works perfectly

       

      Thanks for your help

      Matt