Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Dealing with duplicated values

I think I have a easy one for you guys. We have a table with all suppliers, and in the past was not centralized who would setup them. With that being said we have the following scenario. (it is w...
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi jgeddes ,thanks for the quick reply, I'll add more.

    Hi Anonymous ,

    The Table data is shown below:

    The solution using dax is as follows:

    Use the following DAX expression to create a table

    Table 2 = 
VAR _table =
    SUMMARIZE (
        'Table',
        'Table'[Supplier],
        "CountOfSupplier", COUNTROWS ( 'Table' )
    )
RETURN
    SELECTCOLUMNS (
        ADDCOLUMNS (
            _table,
            "Country",
                IF (
                    [CountOfSupplier] = 1,
                    VAR _supplier = [Supplier]
                    RETURN
                        MAXX ( FILTER ( 'Table', 'Table'[Supplier] = _supplier ), [Country] ),
                    CONCATENATEX (
                        FILTER ( 'Table', 'Table'[Country] <> "-" ),
                        [Country],
                        UNICHAR ( 10 )
                    )
                )
        ),
        [Supplier],
        [Country]
    )

    Final output

     

    Best Regards,
    Wenbin Zhou

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi jgeddes ,thanks for the quick reply, I'll add more.

Hi Anonymous ,

The Table data is shown below:

The solution using dax is as follows:

Use the following DAX expression to create a table

Table 2 = 
VAR _table =
    SUMMARIZE (
        'Table',
        'Table'[Supplier],
        "CountOfSupplier", COUNTROWS ( 'Table' )
    )
RETURN
    SELECTCOLUMNS (
        ADDCOLUMNS (
            _table,
            "Country",
                IF (
                    [CountOfSupplier] = 1,
                    VAR _supplier = [Supplier]
                    RETURN
                        MAXX ( FILTER ( 'Table', 'Table'[Supplier] = _supplier ), [Country] ),
                    CONCATENATEX (
                        FILTER ( 'Table', 'Table'[Country] <> "-" ),
                        [Country],
                        UNICHAR ( 10 )
                    )
                )
        ),
        [Supplier],
        [Country]
    )

Final output

 

Best Regards,
Wenbin Zhou