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?
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 |
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.
