Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Matt22365
Resolver III
Resolver III

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
1 ACCEPTED SOLUTION
johnt75
Super User
Super User

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

View solution in original post

2 REPLIES 2
johnt75
Super User
Super User

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

Amazing!, that works perfectly

 

Thanks for your help

Matt 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.