Split Column without Duplicates

[Anonymous]

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	Google	Dept1, Dept2, Dept4
19	Amazon	Dept2
103	Facebook	Dept3
501	LinkedIn	Dept4, Dept1, Dept3, Dept2
23	Oracle	Dept1

I want to do 2 main things with this:

1. 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. 
2. 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	Google	Dept1	Shared Asset
4	Google	Dept2	Shared Asset
4	Google	Dept4	Shared Asset
19	Amazon	Dept 2	Not Shared
103	Facebook	Dept3	Not Shared
501	LinkedIn	Dept4	Shared Asset
501	LinkedIn	Dept1	Shared Asset
501	LinkedIn	Dept3	Shared Asset
501	LinkedIn	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!

[Ashish_Mathur]

Hi,

Based on the input data you have shared in the first table, show the exact result that you are expecting.

[Anonymous]

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.

[Mariusz]

Hi @Anonymous 

The below Measures will count shared Vendors and Show Shared / Not Shared, Vendors.

Count of shared Vendors = 
VAR __tbl =
FILTER( 
    VALUES( 'Table'[Id] ),
    CALCULATE( 
        COUNTROWS( 'Table' ), 
        ALLEXCEPT( 'Table', 'Table'[Id] ) 
    ) > 1
)
RETURN 
COUNTROWS( __tbl )

Shared/Not Shared = IF( [Count of shared Vendors] > 0, "Shared Asset", "Not Shared" )

 Please see the below screenshot for reference and the attached file.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

[v-lid-msft · Accepted solution]

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,

[Ashish_Mathur]

Hi,

Why do you need a Shared column for that?  Drag Vendor to the row labels and write this measure

=DISTINCTCOUNT(Data[Dept])

Hope this helps.

[VasTg]

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