New Distinct Column that only counts the Value once and it only show it on the 1st duplicate

[Anonymous]

Hi,

I am trying to create a Column that only show the value if is duplicate on the first line. rather than repeating the distinc count on every single row..  I added the earlier on ID and also on RowNumber but get same results.

Show Only Once = CALCULATE(DISTINCTCOUNT('Table1[ID]),'Table1'[SiteID]=EARLIER('table1'[ID]))

Any help will be greatly appreciated Thanks,

RowNumber	ID	DistinctCount	Show only Once
1	5343	2	2
3	5343	2
4	4333	2	2
5	4333	2
6	1333	1	1

[edhans · Accepted solution]

There might be a faster way to do this, but this builds each step one at a time @Anonymous 

First Distinct = 
VAR varCurrentID = [ID]
VAR varCurrentRow = 'DataTable'[RowNumber]
VAR varFirstRow = 
    MINX(
        FILTER(
            'DataTable',
            'DataTable'[ID] = varCurrentID
        ),
        'DataTable'[RowNumber]
    )
VAR varCounts = 
    COUNTX(
        FILTER(
            'DataTable',
            'DataTable'[ID] = varCurrentID
        ),
    'DataTable'[ID]
    )
RETURN
    IF(
        varCurrentRow = varFirstRow,
        varCounts,
        BLANK()
    )

[Anonymous]

Thank You for quick response...Edhans😀

[edhans]

Glad to help @Anonymous