Distinct/ Distinct count by key category

[Anonymous]

Hi everyone,

I need your help😥

I would like to add red column to my table.

Key of the group for distinction would be "show" and "FY".

shop	item	FY	item count	itme name
AAA	Paper Goods	2020	4	Paper Goods,Bread/Bakery ,Frozen Foods,Other
AAA	Bread/Bakery	2020	4	Paper Goods,Bread/Bakery ,Frozen Foods,Other
AAA	Frozen Foods	2020	4	Paper Goods,Bread/Bakery ,Frozen Foods,Other
B	Paper Goods	2020	3	Paper Goods,Bread/Bakery,Personal Care
B	Bread/Bakery	2020	3	Paper Goods,Bread/Bakery,Personal Care
B	Personal Care	2020	3	Paper Goods,Bread/Bakery,Personal Care
AAA	Bread/Bakery	2020	4	Paper Goods,Bread/Bakery ,Frozen Foods,Paper Goods
AAA	Other	2020	4	Paper Goods,Bread/Bakery ,Frozen Foods,Other

[amitchandak]

@Anonymous , try new measures like

calculate(concatenatex(Table,distinct(Table[item])), allexcept(Table[shop]))

or

calculate(concatenatex(Table,values(Table[item])), allexcept(Table[shop]))

https://docs.microsoft.com/en-us/dax/concatenatex-function-dax

calculate(distinctCOUNT(distinct(Table[item]), allexcept(Table[shop]))

[Anonymous]

@amitchandak 

thank you for your help.😀

I've trid but Ive got an error on following 😅

calculate(concatenatex(Table,distinct(Table[item])), allexcept(Table[shop]))

so i change to following.

calculate(concatenatex(Table,distinct(Table[item])), allexcept(Table,Table[shop]))

but when i add to the vidual it shows another error.

"can't display the vidual."

[amitchandak]

@Anonymous , Try like this tested on my table

Measure = CALCULATE(CONCATENATEX(VALUES('Item'[Category]),('Item'[Category])),ALLEXCEPT('Item','Item'[Brand]))

Measure = CALCULATE(CONCATENATEX(VALUES('Table'[Item]),('Table'[Item])),ALLEXCEPT('Table','Item'[shop]))

[Anonymous]

@amitchandak 

Thank you for your help😀

I would like to add a condition shop and FY in one measure.

I've triedlike this but did not work...

Measure = CALCULATE(COUNTX(VALUES('Item'[Item]),('Item'[Item])),ALLEXCEPT('Item','Item'[shop] &&'Item'[FY] ))

Measure = CALCULATE(CONCATENATEX(VALUES('Table'[Item]),('Table'[Item])),ALLEXCEPT('Table','Item'[shop] && 'Item'[FY] ))

[amitchandak · Accepted solution]

@Anonymous , try like

Measure = CALCULATE(COUNTX(VALUES('Item'[Item]),('Item'[Item])),ALLEXCEPT('Item','Item'[shop] , Item'[FY] ))

Measure = CALCULATE(CONCATENATEX(VALUES('Table'[Item]),('Table'[Item])),ALLEXCEPT('Table','Item'[shop] , 'Item'[FY] ))

[Anonymous]

@amitchandak 

that was perfect😂

Thank you so much😌

[Anonymous]

@amitchandak 

Thank you !😆

Would you please advice me how to add a key to distinct?

I would like to distinct shop and FY.

[amitchandak]

@Anonymous , Try like

Measure = CALCULATE(COUNTX(VALUES('Item'[Category]),('Item'[Category])),ALLEXCEPT('Item','Item'[Brand]))