a new column in modeling with aggregation

[dolevh]

Hi All, 

I have this table: 

ID	StartDate	EndDate	Price
123	1/1/21	1/31/21
123	2/1/21	2/28/21	10
123	3/1/21	3/31/21	10
123	4/1/21	4/30/21	10
123	5/1/21	5/16/21
12345	1/1/21	1/31/21
12345	2/1/21	2/28/21
12345	3/1/21	3/31/21	20
12345	4/1/21	4/30/21	20
12345	5/1/21	5/31/21	20
12345	6/1/21	6/30/21	20
123456789	1/1/21	1/31/21
123456789	2/1/21	2/14/21

I need a table with a new column (CountofNum) : 

- If the price is not empty it appears for the first time in each ID card so Table[CountofNum] = '1' 
- If the price is not empty and it appears a second or more time in each ID then Table[CountofNum] = '0'
- else null 

for example:

ID	StartDate	EndDate	Price	CountofNum
123	1/1/21	1/31/21
123	2/1/21	2/28/21	10	1
123	3/1/21	3/31/21	10	0
123	4/1/21	4/30/21	10	0
123	5/1/21	5/16/21
12345	1/1/21	1/31/21
12345	2/1/21	2/28/21
12345	3/1/21	3/31/21	20	1
12345	4/1/21	4/30/21	20	0
12345	5/1/21	5/31/21	20	0
12345	6/1/21	6/30/21	20	0
123456789	1/1/21	1/31/21
123456789	2/1/21	2/14/21

Thanks All!

[jdbuchanan71 · Accepted solution]

That would be like this.

CountofNum = 
VAR _Start =
    CALCULATE (
        MIN ( 'Table'[StartDate] ),
        ALLEXCEPT ( 'Table', 'Table'[ID] )
    )
RETURN
    IF (
        'Table'[Price] = BLANK (),
        BLANK (),
        IF ( 'Table'[StartDate] = _Start, 1, 0 )
    )

[jdbuchanan71]

@dolevh 

Try this as a new calculated column.

CountofNum =
VAR _Start =
    CALCULATE (
        MIN ( 'Table'[StartDate] ),
        ALLEXCEPT ( 'Table', 'Table'[ID] ),
        'Table'[Price] <> BLANK ()
    )
RETURN
    IF (
        'Table'[Price] = BLANK (),
        BLANK (),
        IF ( 'Table'[StartDate] = _Start, 1, 0 )
    )

[dolevh]

and if I want to do a column that shows me just if the 'price' is not null and the 'CountofNum' is 1 and the id appears the first time like this :

ID	StartDate	EndDate	Price	CountofNum
123	1/1/21	1/31/21	20	1
123	2/1/21	2/28/21	10	0
123	3/1/21	3/31/21	10	0
123	4/1/21	4/30/21	10	0
123	5/1/21	5/16/21
12345	1/1/21	1/31/21
12345	2/1/21	2/28/21
12345	3/1/21	3/31/21	20	0
12345	4/1/21	4/30/21	20	0
12345	5/1/21	5/31/21	20	0
12345	6/1/21	6/30/21	20	0
123456789	1/1/21	1/31/21	50	1
123456789	2/1/21	2/14/21	50	0

do you have solution for this? 

thanks so much 🙂