Count if More than one Supplier Sources an Item - Power BI - Boolean Measure

[TylerVono]

Hello, I am currently trying to count the number of items that are sourced from more than one supplier in , I have a boolean column indicating the Item # source. The main table is called: PO Data which also includes the Supplier Column.

There are certain Items that we buy from one of more Supplier, I would like to create a measure that shows those, calling them: "Dual Source", if not then: "Single Source"....

My three attempts below have failed, is there any advice or suggestions that can help? Here are my three attempts.

Attempt 1:

CountTrueValues = sumx('PO Data',if('PO Data'[BooleanItem]= TRUE(),1,0))

Attempt 2:

Attempt2CountwithMultSupplierItems =

VAR ItemSuppliersCounts = summarize('PO Data','PO Data'[BooleanItem],"DistinctSupplierCount", DistinctCount('PO Data'[Supplier]))

RETURN Countrows(Filter(ItemSuppliersCounts,[DistinctSupplierCount]>1))

Attempt 3:

Attempt3CountMultiSupplyItems =

VAR MultiSuppliedCount = filter('PO Data','PO Data'[BooleanSupplier]=TRUE() && calculate(countrows('PO Data'),

filter('PO Data','PO Data'[BooleanItem]=EARLIER('PO Data'[BooleanItem]) && 'PO Data'[BooleanSupplier]=TRUE()))>1)

RETURN countrows(MultiSuppliedCount)

Here are some visuals which might help:

In general, pretty much Attempt 1 and Attempt 3 just count the number of purchase order occurances, and Attempt 2 counts just one occurance. As you can see the example of Item # SD-3912 is a dual sourced item, so the figure should just show 2,i.e. Dual Sourced. I didnt even add the If equation since I fell flat here. 

[Anonymous]

Hi @TylerVono 

I don't know exactly what you mean, do you want to indicate whether you want to count how many item numbers there are different providers, or do you want to show how many different suppliers there are for the same item, if you want to determine that an item has multiple different suppliers, you can try the following formula:

MEASURE=VAR A=CALCULATE(DistinctCount('PO Data'[Supplier]),ALLEXCEPT('PO Data','PO Data'[BooleanItem]))
RETURN IF(A>1,"Dual","Single")

If the information above cannot meet  your requirement, can you provide some sample data or file?

Best Regards!

Yolo Zhu

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