Forum Discussion
Helper ICount Non Matching Rows
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'
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] ) ) )
8 Replies
- Azadsingh
Resolver II
Please share more information, I am not clear what you are expecting in result.
Jtbonner1986i 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
- bhanu_gautam
Super User
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..
- danextian
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] ) ) )
