Count Non Matching Rows

[Jtbonner1986]

Hi, 

Sorry for the basic question here:

I have 2 tables, a pricing table and a material table (joined by material ID). I want to understand where i have a record in my material table but do not have a price for it. I basically want to count the non matching rows in the pricing table when i plot my material table:

EG:

Material Table

Material	Product
123	1
345	1
678	1

Pricing Table:

Material	Price
123	£100
XXX	£50

I want a measure to return a count of '2' when i select Product '1'

[danextian · Accepted solution]

Hi @Jtbonner1986 

Create a one-to-many single direciton relationship from Price to Material

Create this measure:

count = 
CALCULATE (
    COUNTROWS ( Material ),
    KEEPFILTERS ( ISBLANK ( 'Price'[Material] ) )
)

[Jtbonner1986]

I want to add a layer of complexity to this... So my price table can have multiple rows in it (for differing prices & dates) 

I want to essentially do the same (count the blank rows) however, due to the natrue of this structure it now returns '3'

new Price Table:

Material	Price	Date
123	£100	01/01/2024
XXX	£10	01/01/2024
123	£50	02/01/2024

[danextian]

Hi @Jtbonner1986 

Break the relationship between the two tables and try this:

non matching materials = 
COUNTROWS (
    -- EXCEPT returns the set of values in 'Material[Material]' that are not in 'Price[Material]'
    EXCEPT ( VALUES ( Material[Material] ), VALUES ( 'Price'[Material] ) ) 
)

[Jtbonner1986]

This seems to work, however i am always going to have a relationship with the 2 tables

[bhanu_gautam]

@Jtbonner1986 

Create a relationship between the Material Table and the Pricing Table using the Material column.

Create a measure in your Material Table to count the non-matching rows

DAX
NonMatchingCount =
CALCULATE(
COUNTROWS('Material Table'),
ISBLANK(RELATED('Pricing Table'[Price]))
)

[Jtbonner1986]

this syntax is incorrect.. 

[Azadsingh]

Please share more information, I am not clear what you are expecting in result.

[Jtbonner1986]

i want a single card visual displaying the number of materials which do not have a price. in this case material 345 and 678 do not have prices so i would want the result 2