Forum Discussion

yuvalpi's avatar
yuvalpi
Helper I
4 years ago
Solved

count 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 ...
Need Help
  • SpartaBI's avatar
    SpartaBI
    4 years ago

    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

  • SpartaBI's avatar
    SpartaBI
    4 years ago

    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's avatar
yuvalpi
Helper I
4 years ago

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.

 

keydescriptionreleaseunique
111item for salerel12
111item for salerel12
111item for sale 2
111item for sale 2
111item missingrel32

 

thanks again,

yuvalpi 

SpartaBI's avatar
SpartaBI
Community Champion
4 years ago

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