Forum Discussion
Helper Icount unique record based on several columns
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
Helper IHi 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,
Community Championyuvalpi 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
_result
- yuvalpi
Hi again,
I have a followup question.
How can I ingore blanks in the Release column? e.g. for this table to count only the rel1 and rel3, but not the blank value as an item.
key description release unique 111 item for sale rel1 2 111 item for sale rel1 2 111 item for sale 2 111 item for sale 2 111 item missing rel3 2 thanks again,