Forum Discussion
Distinct sum based on two columns
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.
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.
Hi Ahmedx / Dhairya thanks so far, as the users are never happy, how to accomplish this new column?
ThanksDesire 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 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.
