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,

     

Ashish_Mathur's avatar
Ashish_Mathur
Super User
6 years ago

Hi,

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

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.

 

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    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,

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    6 years ago

    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.

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