Unconventional remove duplicate but keep data from another column

[IoannisT]

Greetings to all the Fabric community!

I have the following issue.

Our business database is generating reports without considering "unique key" values, meaning that for some entries, if unique key is the same but different people are involved, it creates 2 rows. The problem becomes more evident on the screenshot below.

For example, Receipt= a1, two people were the "referral" of this sale, if I populate the data on PowerBI, it appears falsely that this department on that day sold 2x mice of 2* $10 each. 

What I would like to see is more aligned with "Solution3" on the screenshot above.

I have tried, remove duplicates, grouping, creating new data model but none of this has worked.

I am creating various visuals like:

*total sales per department per day filter but also a generic head counter on the "Employee_Name".
I understand that ideally there should be a DAX function to equally devide the price per item per referal but this is something we are not exploring at the time.

[v-xinruzhu-msft · Accepted solution]

Hi @IoannisT 

Based on your description, you can refer to the following solution.

Sample data 

You can create a new table first.

New Table =
SUMMARIZE (
    'Table',
    [Receipt_ID],
    'Table'[item_ID],
    'Table'[Quantity],
    'Table'[price],
    'Table'[Department],
    "Name", CONCATENATEX ( 'Table', [Ref.Employee_Name], "," )
)

2.Create a employee_name table

Employee_Name = SUMMARIZE('Table',[Receipt_ID],'Table'[Ref.Employee_Name])

There are no relationships among the tables.

3.Then create a measure

Sum_price =
VAR a =
    COUNTROWS (
        FILTER (
            Employee_Name,
            CONTAINSSTRING ( MAX ( 'New Table'[Name] ), Employee_Name[Ref.Employee_Name] )
        )
    )
RETURN
    CALCULATE (
        SUM ( 'New Table'[price] ),
        FILTER (
            'New Table',
            a > 0
                && 'New Table'[Receipt_ID] IN VALUES ( Employee_Name[Receipt_ID] )
        )
    )

Total_price =
VAR a =
    ADDCOLUMNS ( 'New Table', "sumprice", [Sum_price] )
RETURN
    SUMX (
        FILTER ( a, [Receipt_ID] IN VALUES ( 'New Table'[Receipt_ID] ) ),
        [sumprice]
    )

Then put the following code to the table visual

And put the employee name of employee table to a slicer.

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[IoannisT]

you are a star! Thanks so much!