DAX Help - counting with a 'contains'

Question

Hi there,&nbsp;I have two tables, a Sales table and a User table something like this:&nbsp;SalesCustomer NoOrder NoProduct CodeOrder No &amp; Product CodeQty1111123111-12321111456111-45611111789111-78952222123222-12332222456222-45623333123333-12364444123444-12374444789444-78915555456555-45615555789555-7892&nbsp;UsersCustomer NoUsernameProducts listProduct 123 accessProduct 456 accessProduct 789 access1Jon111-123 |&nbsp;111-456YesYesNo1Jill111-456 |&nbsp;111-789NoYesYes2Bill222-123 |&nbsp;222-456YesYesNo2Bob222-123 |&nbsp;222-456YesYesNo2Susan222-123 |&nbsp;222-456YesYesNo3Sarah333-123YesNoNo4Lisa444-123YesYesNo4Alan444-123 |&nbsp;444-789YesYesYes4Jilly444-789NoNoYes5Jacob555-456 |&nbsp;555-789NoYesNo6William555-456 |&nbsp;555-789NoYesYes&nbsp;Currently there is a linking table of unique Customer Numbers that joins the two tables.&nbsp;What I'm trying to do is identify discrepancies between what sales are recorded in the Sales table and what the allocation of licenses looks like in the Users table.&nbsp;The desired output would look something like this:Customer NoOrder NoProduct CodeQty purchasedQty allocated111112321111145612111178951222212333222245623&nbsp;The "Qty allocated" comes from counting the number of "Yes" values in the column relevant to the Product Code where the User table's Products List contains the same text as the Sales table's&nbsp;Order No &amp; Product Code.&nbsp;There are only 8 different Product Codes so I was considering a nested IF to calculate each one.&nbsp;The closest I've managed to get for Product 123 was this:&nbsp;IF(CONTAINSSTRING('Sales'[Order No &amp; Product Code],"123"),CALCULATE(COUNTROWS('Users'),FILTER('Users',[Product 123 access]="Yes" &amp;&amp; (CONTAINSSTRING('Users'[Products list],"123")))),0)&nbsp;However I get the message: "A single value for column 'Order No &amp; Product Code' in table 'Sales' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."&nbsp;As always, any help from the community is gratefully received.Richard

ashish_mathur · Accepted Answer

Hi,
You may download my PBI file from here.
Hope this helps.

Forum Discussion

DAX Help - counting with a 'contains'

2 Replies

Related content

Count rows containing value

Counting nulls

Contain Word

Count if contains part of text

Counting distinct list against multiple contain strings

Customer No	Order No	Product Code	Order No & Product Code	Qty
1	111	123	111-123	2
1	111	456	111-456	1
1	111	789	111-789	5
2	222	123	222-123	3
2	222	456	222-456	2
3	333	123	333-123	6
4	444	123	444-123	7
4	444	789	444-789	1
5	555	456	555-456	1
5	555	789	555-789	2

Customer No	Username	Products list	Product 123 access	Product 456 access	Product 789 access
1	Jon	111-123 \| 111-456	Yes	Yes	No
1	Jill	111-456 \| 111-789	No	Yes	Yes
2	Bill	222-123 \| 222-456	Yes	Yes	No
2	Bob	222-123 \| 222-456	Yes	Yes	No
2	Susan	222-123 \| 222-456	Yes	Yes	No
3	Sarah	333-123	Yes	No	No
4	Lisa	444-123	Yes	Yes	No
4	Alan	444-123 \| 444-789	Yes	Yes	Yes
4	Jilly	444-789	No	No	Yes
5	Jacob	555-456 \| 555-789	No	Yes	No
6	William	555-456 \| 555-789	No	Yes	Yes