Help counting suppliers based on measure/condition

[Manudata]

This is the problem:

My tables are linked like this

TABLE_SUPPLIERS_PRODUCT_MANAGER.CodeManager    =   TABLE_ORDERS.CodeManager

TABLE_SUPPLIERS_PRODUCT_MANAGER

CodeSupplier	CodeProduct	CodeManager (PK)	Have Orders
A	Tables	Manager 001	Y
A	Chairs	Manager 002	N
A	Chairs	Manager 003	Y
B	Tables	Manager 004	Y
B	Tables	Manager 005	N
B	Chairs	Manager 006	Y
B	Shelves	Manager 007	N
C	Shelves	Manager 008	N

TABLE_ORDERS

OrderNumber	CodeManager	CodeProduct	Units
0001	Manager 001	Tables	10
0002	Manager 001	Tables	1
0003	Manager 003	Chairs	10
0004	Manager 006	Chairs	20
0005	Manager 004	Tables	20

So, I have the following measure:

No. Registered suppliers = DISTINCTCOUNT(TABLE_SUPPLIERS_PRODUCT_MANAGER[CodeSupplier])

No. Registered suppliers with orders= 
CALCULATE([No. Registered suppliers], FILTER(TABLE_SUPPLIERS_PRODUCT_MANAGER, TABLE_SUPPLIERS_PRODUCT_MANAGER[HaveOrders]="Y"))

And now I want:

• No. Registered suppliers without orders
• And the possibility of seeing who they are on a list
• One question: User can filter by CodeProduct

This measure not works

No. Registered suppliers without orders= CALCULATE([No. Registered suppliers], FILTER(TABLE_SUPPLIERS_PRODUCT_MANAGER, TABLE_SUPPLIERS_PRODUCT_MANAGER[HaveOrders]="N"))

Because this includes suppliers who, perhaps with another product have orders.

With the data I have provided, this value should give

• No. Registered suppliers with orders: 2
• No. Registered suppliers without orders: 1

If the user filters to see only SHELVES products, the measurements would be

• No. Registered suppliers with orders: 0
• No. Registered suppliers without orders: 1

If the user filters to see only CHAIRS products, the measurements would be

• No. Registered suppliers with orders: 2
• No. Registered suppliers without orders: 0

[v-zhangti · Accepted solution]

Hi, @Manudata 

You can try the following methods.

Column:

Column YN = CALCULATE(MAX(TABLE_SUPPLIERS_PRODUCT_MANAGER[Have Orders]),ALLEXCEPT(TABLE_SUPPLIERS_PRODUCT_MANAGER,TABLE_SUPPLIERS_PRODUCT_MANAGER[CodeSupplier]))

Measure Y = CALCULATE(DISTINCTCOUNT(TABLE_SUPPLIERS_PRODUCT_MANAGER[CodeSupplier]),FILTER(ALL(TABLE_SUPPLIERS_PRODUCT_MANAGER),[CodeProduct]=MAX(TABLE_SUPPLIERS_PRODUCT_MANAGER[CodeProduct])&&[Have Orders]="Y"))+0

Measure N = 
Var _N1=CALCULATE(DISTINCTCOUNT(TABLE_SUPPLIERS_PRODUCT_MANAGER[CodeSupplier]),FILTER(ALL(TABLE_SUPPLIERS_PRODUCT_MANAGER),[CodeProduct]=MAX(TABLE_SUPPLIERS_PRODUCT_MANAGER[CodeProduct])&&[Have Orders]="N"))+0
Var _N2=CALCULATE(DISTINCTCOUNT(TABLE_SUPPLIERS_PRODUCT_MANAGER[CodeSupplier]),FILTER(ALL(TABLE_SUPPLIERS_PRODUCT_MANAGER),[CodeProduct]=MAX(TABLE_SUPPLIERS_PRODUCT_MANAGER[CodeProduct])&&[Column YN]="N"))+0
Return
IF(SELECTEDVALUE(TABLE_SUPPLIERS_PRODUCT_MANAGER[CodeProduct])=BLANK(),_N1,_N2)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[speedramps]

Create this measure

Has orders =

// If context has any orders then return 1, else return null

INT(NOT(ISEMPTY(TABLE_ORDERS)))

No. Registered suppliers with orders =

// create a temporary table of just suppliers with orders

VAR suppliers_without_orders = FILTER(VALUES(TABLE_SUPPLIERS_PRODUCT_MANAGER[CodeSupplier]),[Has orders] = 1)

RETURN

// count the suppliers on the temporary table

COUNTROWS(suppliers_without_orders)

No. Registered suppliers without orders =

// create a temporary table of just suppliers with no orders

VAR suppliers_without_orders = FILTER(VALUES(TABLE_SUPPLIERS_PRODUCT_MANAGER[CodeSupplier]),[Has orders] = 0)

RETURN

// count the suppliers on the temporary table

COUNTROWS(suppliers_without_orders)

To see which suppliers are on a list,  use the [Has orders] as a filter

This solution will alow you to add a product silicer

Thanks for the clear description of the problem with example data. I wish everyone did that!

Remember we are unpaid volunteers. So please click the thumbs up and the [accept as solution] button to leave kudos. 

One question per ticket please. If you need to extend your request then please raise a new ticket.

If you quote @speedramos in your next tickets then I will then receive an automatic notification, and will be delighted to help you again.

Please click the thumbs up and the [accept as solution] button now.  Thank you.