Distinct sum based on two columns

[Aucesar]

Hi Community,

Tried to count how many products the employee worked, sounds easy at the beggining but I cant count the second column.

Tried usuing

DISTINCTCOUNTNOBLANK, but I can´t tell PBI to merge shift1+shift2 and give me a single result.

Also tried some suggestions with sumx but the data is a string, and I got errors.

Thanks in advance for any help...

			Desire results
Product	Shift1	Shift2	Name	ProductsWorked
a	Wade	Brian	Wade	2
b	Dave	Roberto	Dave	3
c	Seth	Ramon	Seth	4
d	Brian		Brian	3
e	Roberto	Riley	Roberto	2
f	Ramon	Gilbert	Ramon	3
g	Jorge	Gilbert	Jorge	1
h		Dan	Gilbert	5
i	Brian	Wade	Riley	1
j	Roberto	Dave	Dan	1
k	Ramon	Seth
l	Ramon	Brian
m		Dave
n	Gilbert	Ramon
o	Gilbert	Seth
p	Gilbert	Seth

[Ahmedx]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

my solution in DAX

https://1drv.ms/u/s!AiUZ0Ws7G26Rigc-Q_c7tkx3fX-1?e=vdm3x6

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

[Aucesar]

Hi @Ahmedx / @Dhairya thanks so far, as the users are never happy, how to accomplish this new column?
Thanks

				Desire results
ProductionBatch	Product	Shift1	Shift2	Name	ProductsWorked	ProductionBatchWorked
XX01	a	Wade	Brian	Wade	2	1
XX01	b	Dave	Roberto	Dave	3	3
XX01	c	Seth	Ramon	Seth	4	3
XX01	d	Brian	Wade	Brian	3	2
XX02	e	Roberto	Riley	Roberto	2	2
XX02	f	Ramon	Gilbert	Ramon	3	3
XX02	g	Jorge	Gilbert	Jorge	1	1
XX02	h		Dan	Gilbert	5	3
XX02	i	Brian	Ramon	Riley	1	1
XX02	j	Roberto	Dave	Dan	1	1
XX02	k	Ramon	Brian
XX03	l	Ramon	Seth
XX03	m		Dave
XX03	n	Gilbert	Ramon
XX03	o	Gilbert	Seth
XX03	p	Gilbert	Seth

[v-xinruzhu-msft · Accepted solution]

Hi @Aucesar 

You can refer to the following measure

1.Create a table to diaplay the user

2.Then create a measure

ProductionBatchWorked = var a=SUMMARIZE(ALLSELECTED('Table'),[ProductionBatch],[Shift1])
var b=SUMMARIZE(ALLSELECTED('Table'),[ProductionBatch],[Shift2])
var c=SUMMARIZE(UNION(a,b),'Table'[Shift1],"Distinct",DISTINCTCOUNTNOBLANK('Table'[ProductionBatch]))
return MAXX(FILTER(c,[Shift1] in VALUES('Table 2'[Shift2])),[Distinct])

Output

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.

[Aucesar]

Hi @v-xinruzhu-msft , @Ahmedx , @Dhairya  thank you all! Worked like a champ!

[Dhairya]

Hey @Aucesar 
If the provided solution helps you then please give it a kudo and mark my solution as accepted so that other can find it quickly on facing similar issue. Thank You!

[Dhairya]

Hey @Aucesar 
You have to transform your data first please follow the below steps

Input:

Step1: Open Power Query Editor and unpivot Shift1 and Shift2 columns, and click on close and apply
Output:

Step2: Now create following measure

S1 = 
CALCULATE(
    COUNTX(TableA,TableA[Product]), 
    ALLEXCEPT(TableA,TableA[Value])
)

Step3: Plot your expected columns you will get the following output

If this helps you then please mark my solution as accepted so that others can find it quickly while facing similar issue. Thank You!