Forum Discussion
Split Column without Duplicates
Hi,
I'm pretty new to PowerBI so any help would be appreciated. I have a dataset (called 'Asset Reg') coming from a Sharepoint list which gives me the below data.
Id | Vendor | Dept |
4 | Dept1, Dept2, Dept4 | |
19 | Amazon | Dept2 |
103 | Dept3 | |
501 | Dept4, Dept1, Dept3, Dept2 | |
23 | Oracle | Dept1 |
I want to do 2 main things with this:
- I want to filter on Dept without the commas. For instance, If I create a slicer and select Dept 2, then Google, Amazon & LinkedIn should show up.
- I want to show which vendors are 'shared' across departments. essentially anything with more than 1 dept is shared. Google & LinkedIn are shared while Amazon, Facebook & Oracle are not.
What I did: I split the columns by comma delimiter to identify the individual dept (this worked perfectly). Next, I wanted to find the 'shared' vendors. To do this, I used the below calculated column to say any column with the ID listed more than once is ‘shared’
Shared=
IF (
COUNTROWS ( FILTER ('Asset Reg', 'Asset Reg'[Id] = EARLIER ( 'Asset Reg'[Id] ) ) )
> 1,
"Shared Vendor",
"Not Shared"
)
This gives me the following output in powerbi:
Id | Vendor | Dept | Shared |
4 | Dept1 | Shared Asset | |
4 | Dept2 | Shared Asset | |
4 | Dept4 | Shared Asset | |
19 | Amazon | Dept 2 | Not Shared |
103 | Dept3 | Not Shared | |
501 | Dept4 | Shared Asset | |
501 | Dept1 | Shared Asset | |
501 | Dept3 | Shared Asset | |
501 | Dept2 | Shared Asset | |
23 | Oracle | Dept1 | Not Shared |
My only thing is, if I create a visual on the Shared column, my numbers will be inflated. I tried to search for dax measures that would only count the first occurrence of a duplicate (by ID #) and ignore the others. So using the example above, Google should be listed as ‘shared’ once for Dept1 and the subsequent Google entries (Dept2, Dept4) should be ignored. Same for LinkedIn.
I also tried to duplicate the table and group by ID and delete the duplicates but the calculated column changed to 'not shared' for every vendor listed.
Any help would be appreciated!
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,
6 Replies
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 Google Dept1, Dept2, Dept4 10 3 19 Amazon Dept2 20 1 103 Facebook Dept3 30 1 501 LinkedIn 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 Google Dept1, Dept2, Dept4 10 3 TRUE 19 Amazon Dept2 20 1 FALSE 103 Facebook Dept3 30 1 FALSE 501 LinkedIn 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.
Hi,
Based on the input data you have shared in the first table, show the exact result that you are expecting.
Hi thanks for responding.
The desired outcome would be a slicer with:
Dept 1
Dept 2
Dept 3
Dept 4
So if I selected Dept 2, all vendors that belonged to Dept 2 would show up in my data. Which i'm able to achieve via 'split to row' option in Powerbi.
I would like an output that looks similar to the below:
Id
Vendor
Dept
Shared
4
Google
Dept1
Shared Asset
4
Google
Dept2
4
Google
Dept4
19
Amazon
Dept 2
Not Shared
103
Facebook
Dept3
Not Shared
501
LinkedIn
Dept4
Shared Asset
501
LinkedIn
Dept1
501
LinkedIn
Dept3
501
LinkedIn
Dept2
23
Oracle
Dept1
Not Shared
The main thing i'm trying to determine is what vendors are being used by 1 department (not shared) vs vendors being used by multiple departments(shared). The only way I can think to do this would be to tell PowerBi "any vendor that has more than 1 department is a shared service". and I just need the first occurrence flagged so that I don't have duplicates. I don't want my numbers to inflate because a vendor with 3 departments will show up 3x. I tried using distinct count and the numbers still weren't right.
I'm open to a different approach if there is one that makes more sense/easier. I hope I cleared that up and I appreciate your help. Please let me know if you need more detail.
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,
