DAX - Finding the possible combination for all items

[ngct1112]

Hi All, I am trying to solve a problem in the PowerBI model.

There are 15 items need to be produced. I have classified the Suppliers from the cheapest to the most expensive.

I am now trying to determine the bundled production with 1 supplier or 2 supplier or more.

Dateset:

ID	Top 1 Supplier	Top 2 Supplier	Top 3 Supplier
1	Supplier A	Supplier B	Supplier C
2	Supplier B	Supplier A
3	Supplier C	Supplier A
4	Supplier A	Supplier B	Supplier C
5	Supplier A	Supplier B
6	Supplier B	Supplier C	Supplier A
7	Supplier B
8	Supplier A	Supplier B	Supplier C
9	Supplier A
10	Supplier A	Supplier C	Supplier B
11	Supplier A	Supplier B	Supplier C
12	Supplier B	Supplier A
13	Supplier C	Supplier A	Supplier B
14	Supplier B	Supplier C	Supplier A
15	Supplier B	Supplier C

I am trying to find out the questions below,

1.) Is it possible only by using one Supplier could produce all those items?   Answer: No(Null)

2.) Is it possible by using 2 Suppliers could produce all those items? if yes, which two?   Answer: A & B

It is quite a tricky question and i have been trying to finding a solution for so long. Appreciated with any helps. Thanks.

[Ashish_Mathur]

Hi,

Your requirement is not clear.  Why should the answer be 15 products for A&B?  Supplier A cannot product Product 7.  Please explain.

[ngct1112]

Thanks @Ashish_Mathur I would like to find the maximum products each combination could produce, hope this Table could explain better:

ID	Top 1 Group	Top 2 Group	Top 3 Group		Supplier A	Supplier B	Supplier C	Supplier A or B	Supplier B or C	Supplier A or C
1	Supplier A	Supplier B	Supplier C		Y	Y	Y	Y	Y	Y
2	Supplier B	Supplier A			Y	Y	N	Y	Y	Y
3	Supplier C	Supplier A			Y	N	Y	Y	Y	Y
4	Supplier A	Supplier B	Supplier C		Y	Y	Y	Y	Y	Y
5	Supplier A	Supplier B			Y	Y	N	Y	Y	Y
6	Supplier B	Supplier C	Supplier A		Y	Y	Y	Y	Y	Y
7	Supplier B				N	Y	N	Y	Y	N
8	Supplier A	Supplier B	Supplier C		Y	Y	Y	Y	Y	Y
9	Supplier A				Y	N	N	Y	N	Y
10	Supplier A	Supplier C	Supplier B		Y	Y	Y	Y	Y	Y
11	Supplier A	Supplier B	Supplier C		Y	Y	Y	Y	Y	Y
12	Supplier B	Supplier A			Y	Y	N	Y	Y	Y
13	Supplier C	Supplier A	Supplier B		Y	Y	Y	Y	Y	Y
14	Supplier B	Supplier C	Supplier A		Y	Y	Y	Y	Y	Y
15	Supplier B	Supplier C			N	Y	Y	Y	Y	Y
					13	13	10	15	14	14

Desired Outcome:

Combination:	Products
Supplier A	13
Supplier B	13
Supplier C	10
Supplier A or B	15
Supplier B or C	14
Supplier A or C	14

[Ashish_Mathur]

Hi,

I am still not clear.  You are saying that whether i hire Supplier A or B, i will be able to produce 15 items.  That is clearly not correct because Supplier A cannot produce item 7.

[v-lili6-msft · Accepted solution]

hi  @ngct1112 

For you want to show Combination Supplier, I would suggest you create measure for each output

Supplier A = CALCULATE(DISTINCTCOUNT(Supplier[ID]),FILTER(Supplier,Supplier[Supplier] = "Supplier A"))

Supplier B = CALCULATE(DISTINCTCOUNT(Supplier[ID]),FILTER(Supplier,Supplier[Supplier] = "Supplier B"))

Supplier C = CALCULATE(DISTINCTCOUNT(Supplier[ID]),FILTER(Supplier,Supplier[Supplier] = "Supplier C"))

Supplier A&B = CALCULATE(DISTINCTCOUNT(Supplier[ID]),FILTER(Supplier,Supplier[Supplier] IN {"Supplier A", "Supplier B"}))

Supplier A&C = CALCULATE(DISTINCTCOUNT(Supplier[ID]),FILTER(Supplier,Supplier[Supplier] IN {"Supplier A", "Supplier C"}))

Supplier B&C = CALCULATE(DISTINCTCOUNT(Supplier[ID]),FILTER(Supplier,Supplier[Supplier] IN {"Supplier C", "Supplier B"}))

Result:

or another way as below:

Step1 :

Use this formula to create a dim Combination table

Dim Supplier = 
var _supplier1=SELECTCOLUMNS(VALUES(Supplier[Supplier]),"Supplier 1",[Supplier])
var _supplier2=SELECTCOLUMNS(VALUES(Supplier[Supplier]),"Supplier 2",[Supplier])
return

ADDCOLUMNS(GENERATE(_supplier1,_supplier2),
"Combination",CONCATENATEX(DISTINCT(UNION(ROW("Supplier",[Supplier 1]),ROW("Supplier",[Supplier 2]))),[Supplier]," or ",[Supplier],ASC))

Step2:

Create a measure as below:

Products Measure =
 CALCULATE(DISTINCTCOUNT(Supplier[ID]),FILTER(Supplier,Supplier[Supplier] in VALUES('Dim Supplier'[Supplier 1])||Supplier[Supplier] in VALUES('Dim Supplier'[Supplier 2])))

Step3:

Now drag Combination field from dim table and this meausre into visual

here is sample pbix file, please try it.

Regards,

Lin

[ngct1112]

@v-lili6-msft Your solution is indeed so brilliant. Appreciated with your great help. I will take method 1 in my model.

[amitchandak]

@ngct1112 , is it only three or this just sample, you can have n number suppliers and have top3

[amitchandak]

@ngct1112 , I unpivoted the table and created one analysis. That can answer, your questions

[ngct1112]

@amitchandak. Thanks for it, but how could the table show Supplier A & B are sufficient to produce all 15 products?

The table is showing how many products each supplier can produce respectively in the "products" column?

Is it possible to show something like 

Supplier A: 13

Supplier B: 13

Supplier C: 10

A & B: 15

A & C: 14

[ngct1112]

Thanks @amitchandak you are correct. This is just a sample and there could be different number of supplier for different project.

Some times it may take more supplier to fulfil to production.