Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Distinct/ Distinct count by key category

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

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

View solution in original post

8 REPLIES 8
amitchandak
Super User
Super User

@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
Not applicable

@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."

 

 

@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
Not applicable

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

@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
Not applicable

@amitchandak 

that was perfect😂

Thank you so much😌

Anonymous
Not applicable

@amitchandak 

 

Thank you !😆

 

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

I would like to distinct shop and FY.

 

 

@Anonymous , Try like

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors