count unique record based on several columns

[yuvalpi]

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!

[SpartaBI · Accepted solution]

@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
    _result

      

Showcase Report – Contoso By SpartaBI

[yuvalpi]

@SpartaBI 

WOW - that's exactly what I needed.

thank a lot! now I need to understand it myself... 😊

[SpartaBI]

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

      

Showcase Report – Contoso By SpartaBI

[yuvalpi]

Hi @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,

@yuvalpi 

[SpartaBI · Accepted solution]

@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
    _result

      

Showcase Report – Contoso By SpartaBI

[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,

@yuvalpi 

[SpartaBI · Accepted solution]

@yuvalpi 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

      

Showcase Report – Contoso By SpartaBI

[yuvalpi]

Yes, exactly!

thank you much, again!

@yuvalpi 

[SpartaBI]

@yuvalpi my pleasure :))

Really, check out this report:

https://community.powerbi.com/t5/Data-Stories-Gallery/Contoso-by-SpartaBI/m-p/2449543

[SpartaBI]

@yuvalpi 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

      

Showcase Report – Contoso By SpartaBI