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 Store Fruit 1 Fruit 2 Fruit 3 Fruit 4 Fruit 5 A orange orange apple   banana B apple dragonfruit banana peach ...
  • 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]
                )
            )
        )
)
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]
                )
            )
        )
)