Forum Discussion
Split Column without Duplicates
Hi Anonymous ,
we can try to add a calculated column to meet your requirement:
Shared = IF ( CALCULATE ( DISTINCTCOUNT ( 'Asset Reg'[Dept] ), FILTER ( 'Asset Reg', 'Asset Reg'[Id] = EARLIER ( 'Asset Reg'[Id] ) ) ) > 1, IF ( [Dept] = CALCULATE ( MIN ( 'Asset Reg'[Dept] ), FILTER ( 'Asset Reg', 'Asset Reg'[Id] = EARLIER ( 'Asset Reg'[Id] ) ) ), "Shared Asset", BLANK () ), "Not Shared" )
Best regards,
Anonymous
I would use the below M query to get the number of departments and use the value to divide the sales(or any summation measures) before pivoting the Dept column
Custom=List.Count(Text.Split([Dept],","))
| ID | Vendor | Dept | Sales | Custom | Sales/Custom |
| 4 | Dept1, Dept2, Dept4 | 10 | 3 | ||
| 19 | Amazon | Dept2 | 20 | 1 | |
| 103 | Dept3 | 30 | 1 | ||
| 501 | Dept4, Dept1, Dept3, Dept2 | 40 | 4 | ||
| 23 | Oracle | Dept1 | 50 | 1 |
You could also use distinct count in the visual for any count measures.
If you don't want to pivot the table, you could use the below query to get True/False (Replace True with Shared and False with "Not shared").
Custom.1=Text.Contains([Dept], ",")
Or a simple conditional column => If Custom = 1 "Not Shared" else "Shared"
| ID | Vendor | Dept | Sales | Custom | Custom.1 |
| 4 | Dept1, Dept2, Dept4 | 10 | 3 | TRUE | |
| 19 | Amazon | Dept2 | 20 | 1 | FALSE |
| 103 | Dept3 | 30 | 1 | FALSE | |
| 501 | Dept4, Dept1, Dept3, Dept2 | 40 | 4 | TRUE | |
| 23 | Oracle | Dept1 | 50 | 1 | FALSE |
After that you could split the Dept column and Pivot the table. The only advantage in pivoting the table is when you use Dept as a slicer, it will show individual departments intead of all the departments for ID, Vendor combination.
If this helps, mark it as a solution
Kudos are nice too.