Distinct Count

[QAA91]

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	plum
C	pear	jackfruit	pear	pear	jackfruit
D	watermelon	rapsberry	grape		grape

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

Table B

Store	Distinct Fruit By Rows	Total Distinct Fruit For All The Store
A	3	11
B	5
C	2
D	3

Many thanks for your help.

[Jihwan_Kim · Accepted solution]

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]
                )
            )
        )
)

[amitchandak]

@QAA91 ,I think it is better, you unpivot the fruits column and then these calculation would be easy

https://radacad.com/pivot-and-unpivot-with-power-bi