Forum Discussion
Helper Icount unique record based on several columns
hi,
i have a question regarding unique records.
i have the following table:
| key | description | release |
| 111 | item for sale | rel1 |
| 111 | item for sale | rel1 |
| 111 | item for sale | rel2 |
| 111 | item for sale | rel2 |
| 111 | item missing | rel3 |
| 222 | 2nd item | rel1 |
| 222 | 2nd item | rel1 |
| 222 | 2nd item | rel1 |
| 222 | name change | rel1 |
| 222 | name change | rel2 |
i would like to add a row that counts the unique records accoding to column 'Key' and column 'Release'.
the outcome should be:
| key | description | release | unique |
| 111 | item for sale | rel1 | 3 |
| 111 | item for sale | rel1 | 3 |
| 111 | item for sale | rel2 | 3 |
| 111 | item for sale | rel2 | 3 |
| 111 | item missing | rel3 | 3 |
| 222 | 2nd item | rel1 | 2 |
| 222 | 2nd item | rel1 | 2 |
| 222 | 2nd item | rel1 | 2 |
| 222 | name change | rel1 | 2 |
| 222 | name change | rel2 | 2 |
Thanks in advance!
yuvalpi OKK 🙂 So I didn't understand what you meant.
This is the column you need:Column 2 = VAR _current_key = 'Table'[key] VAR _result = CALCULATE( DISTINCTCOUNT( 'Table'[release] ), REMOVEFILTERS('Table'), 'Table'[key] = _current_key ) RETURN _resultyuvalpi you mean like this?:
Column 2 = VAR _current_key = 'Table'[key] VAR _result = CALCULATE( DISTINCTCOUNT( 'Table'[release] ), REMOVEFILTERS('Table'), 'Table'[key] = _current_key, 'Table'[release] <> BLANK() ) RETURN _result
10 Replies
- SpartaBI
Community Champion
yuvalpi my pleasure 🙂 Will appreciate your kudos :))
Hey, check out my showcase report - got some high level stuff there. Sure you will find there a lot of cool ideas.
https://community.powerbi.com/t5/Data-Stories-Gallery/SpartaBI-Feat-Contoso-100K/td-p/2449543
Give it a thumbs up over there if you liked it 🙂
- SpartaBI
yuvalpi this is a column that counts the number of rows that has the same key and release:
Column = VAR _current_key = 'Table'[key] VAR _current_release = 'Table'[release] VAR _result = COUNTROWS( FILTER( 'Table', 'Table'[key] = _current_key && 'Table'[release] = _current_release ) ) RETURN _result
I'm not sure how you got to your result in the example, so maybe I didn't understand what you need.
yuvalpiHi SpartaBI
first - thank you for the swift reply!
regarding the solution, I entered into the BI, but I am not sure it is what I needed.
per my example - I wanted to get per row the number of unique values that meet the criteria.
in the example that I sent, if I focus only on Key 111, I can see 3 different releases (rel1, rel2, rel3), hence I wanted to show the number 3 in all the relevant rows.
key description release unique 111 item for sale rel1 3 111 item for sale rel1 3 111 item for sale rel2 3 111 item for sale rel2 3 111 item missing rel3 3 Thank you again,