Forum Discussion

QAA91's avatar
QAA91
Regular Visitor
4 years ago
Solved

Distinct Count

Hi,

 

I have sample of data that looks like this:

 

Table A

StoreFruit 1Fruit 2Fruit 3Fruit 4Fruit 5
Aorangeorangeapple banana
Bappledragonfruitbananapeachplum
Cpearjackfruitpearpearjackfruit
Dwatermelonrapsberrygrape grape

 

And I would like to create a new table where the output looks like this:

Table B

StoreDistinct Fruit By RowsTotal Distinct Fruit For All The Store
A311
B5 
C2 
D3 

 

Many thanks for your help.

  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    Please check the below picture and the attached pbix file.

    It is for creating a new table.

     

     

    New Table B = 
ADDCOLUMNS (
    VALUES ( 'Table A'[Store] ),
    "@DistinctFruitByRow",
        COUNTROWS (
            SUMMARIZE (
                FILTER (
                    UNION (
                        SELECTCOLUMNS ( 'Table A',"@Store", 'Table A'[Store], "@Fruit", 'Table A'[Fruit 1] ),
                        SELECTCOLUMNS ( 'Table A',"@Store", 'Table A'[Store], "@Fruit", 'Table A'[Fruit 2] ),
                        SELECTCOLUMNS ( 'Table A',"@Store", 'Table A'[Store], "@Fruit", 'Table A'[Fruit 3] ),
                        SELECTCOLUMNS ( 'Table A',"@Store", 'Table A'[Store],"@Fruit", 'Table A'[Fruit 4] ),
                        SELECTCOLUMNS ( 'Table A',"@Store", 'Table A'[Store], "@Fruit", 'Table A'[Fruit 5] )
                    ),
                    [@Store] = EARLIER ( 'Table A'[Store] )
                        && [@Fruit] <> BLANK ()
                ),
                [@Fruit]
            )
        ),
    "@TotalDistinctFruit",
        IF (
            'Table A'[Store] = "A",
            COUNTROWS (
                SUMMARIZE (
                    FILTER (
                        UNION (
                            SELECTCOLUMNS ( 'Table A',"@Store", 'Table A'[Store], "@Fruit", 'Table A'[Fruit 1] ),
                        SELECTCOLUMNS ( 'Table A',"@Store", 'Table A'[Store], "@Fruit", 'Table A'[Fruit 2] ),
                        SELECTCOLUMNS ( 'Table A',"@Store", 'Table A'[Store], "@Fruit", 'Table A'[Fruit 3] ),
                        SELECTCOLUMNS ( 'Table A',"@Store", 'Table A'[Store],"@Fruit", 'Table A'[Fruit 4] ),
                        SELECTCOLUMNS ( 'Table A',"@Store", 'Table A'[Store], "@Fruit", 'Table A'[Fruit 5] )
                        ),
                        [@Fruit] <> BLANK ()
                    ),
                    [@Fruit]
                )
            )
        )
)

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

    Hi,

    Please check the below picture and the attached pbix file.

    It is for creating a new table.

     

     

    New Table B = 
ADDCOLUMNS (
    VALUES ( 'Table A'[Store] ),
    "@DistinctFruitByRow",
        COUNTROWS (
            SUMMARIZE (
                FILTER (
                    UNION (
                        SELECTCOLUMNS ( 'Table A',"@Store", 'Table A'[Store], "@Fruit", 'Table A'[Fruit 1] ),
                        SELECTCOLUMNS ( 'Table A',"@Store", 'Table A'[Store], "@Fruit", 'Table A'[Fruit 2] ),
                        SELECTCOLUMNS ( 'Table A',"@Store", 'Table A'[Store], "@Fruit", 'Table A'[Fruit 3] ),
                        SELECTCOLUMNS ( 'Table A',"@Store", 'Table A'[Store],"@Fruit", 'Table A'[Fruit 4] ),
                        SELECTCOLUMNS ( 'Table A',"@Store", 'Table A'[Store], "@Fruit", 'Table A'[Fruit 5] )
                    ),
                    [@Store] = EARLIER ( 'Table A'[Store] )
                        && [@Fruit] <> BLANK ()
                ),
                [@Fruit]
            )
        ),
    "@TotalDistinctFruit",
        IF (
            'Table A'[Store] = "A",
            COUNTROWS (
                SUMMARIZE (
                    FILTER (
                        UNION (
                            SELECTCOLUMNS ( 'Table A',"@Store", 'Table A'[Store], "@Fruit", 'Table A'[Fruit 1] ),
                        SELECTCOLUMNS ( 'Table A',"@Store", 'Table A'[Store], "@Fruit", 'Table A'[Fruit 2] ),
                        SELECTCOLUMNS ( 'Table A',"@Store", 'Table A'[Store], "@Fruit", 'Table A'[Fruit 3] ),
                        SELECTCOLUMNS ( 'Table A',"@Store", 'Table A'[Store],"@Fruit", 'Table A'[Fruit 4] ),
                        SELECTCOLUMNS ( 'Table A',"@Store", 'Table A'[Store], "@Fruit", 'Table A'[Fruit 5] )
                        ),
                        [@Fruit] <> BLANK ()
                    ),
                    [@Fruit]
                )
            )
        )
)