Forum Discussion
PatrickWong
Helper I
Helper ICounting unique based on 2 factors
I need help in powerbi table. The purpose is to determine how many unique item is in the box
raw dat is in first 2 column, while the desired outcome is on column 3,4 and 5
Column 5 will result to yes if there is only 1 unique item in the box (max count =1), if the box max count is more than 1, then column 5 will be a no.
- Max Count =
VAR __Table = SUMMARIZE(FILTER('Table (2)',[Box] = EARLIER('Table (2)'[Box])),[Item])
RETURN
COUNTROWS(__Table)
3 Replies
- Greg_Deckler
Community Champion
Unique Column =
VAR __Table = SUMMARIZE(FILTER('Table (2)',[Box] = EARLIER('Table (2)'[Box])),[Item])
RETURN
IF(COUNTROWS(__Table) = 1,"Yes","No") - Greg_DecklerMax Count =
VAR __Table = SUMMARIZE(FILTER('Table (2)',[Box] = EARLIER('Table (2)'[Box])),[Item])
RETURN
COUNTROWS(__Table) - Greg_Deckler
Distinct count isn't really possible given your current data layout. You would need an Index column or something like it.