Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 ...
Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    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's avatar
Anonymous
Not applicable
6 years ago

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's avatar
Mariusz
Community Champion
6 years ago

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.