topic Re: Distinct Count in DAX Commands and Tips

Distinct Count

QAA91 — Fri, 29 Apr 2022 03:43:01 GMT

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.

Re: Distinct Count

amitchandak — Fri, 29 Apr 2022 04:09:56 GMT

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

Re: Distinct Count

Jihwan_Kim — Fri, 29 Apr 2022 04:22:14 GMT

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