Forum Discussion

dolevh's avatar
dolevh
Icon for Helper II rankHelper II
4 years ago
Solved

a new column in modeling with aggregation

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/3...
Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    4 years ago

    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's avatar
jdbuchanan71
Icon for Super User rankSuper User
4 years ago

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's avatar
    dolevh
    Icon for Helper II rankHelper II
    4 years ago

    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 🙂