Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Generating new table based on condition

Hi team, I am quite new to POWER BI so keen to know how to figure this out: 

I have a table below, what I am trying to do is finding the latest two Credit_rating records for each account( this is decided by last_process_time). 

I had created two columns to identify the latest two records, IsLastProday column and IsOldrecord column,

Now I want to get a table like below:, I am trying to use summarize function 

CreditChange = SUMMARIZE('table','table'[Account],"IslastProDay",if('table'[IsLastProDay]="Yes",'table'[Credit_rating]),"IsOldRecord",if('table'[IsOldrecord]="Yes",'table'[Credit_rating]))

but getting error saying the single Value of column 'Credit_rating' in table cannot be determined. 

 

Anyone have a better idea how can I get the table above?? thanks so much 🙂

 

 

Need Help
  • ryan_mayu's avatar
    ryan_mayu
    5 years ago

    Anonymous 

    maybe you can try this

    Table 2 = 

VAR tbl=ADDCOLUMNS(SUMMARIZE('Table','Table'[Account],"last",CALCULATE(max('Table'[Last_process_time]),ALLEXCEPT('Table','Table'[Account])),"last2",maxx(FILTER('Table','Table'[Account]=EARLIER('Table'[Account])&&'Table'[Last_process_time]<CALCULATE(max('Table'[Last_process_time]),ALLEXCEPT('Table','Table'[Account]))),'Table'[Last_process_time])),"isoldrecord",maxx(FILTER('Table','Table'[Account]=EARLIER('Table'[Account])&&'Table'[Last_process_time]=[last]),'Table'[Credit_rating]),"islastproday",maxx(FILTER('Table','Table'[Account]=EARLIER('Table'[Account])&&'Table'[Last_process_time]=[last2]),'Table'[Credit_rating]))
RETURN SUMMARIZE(tbl,'Table'[Account],[isoldrecord],[islastproday])

    please see the attachment below.

1 Reply

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    5 years ago

    Anonymous 

    maybe you can try this

    Table 2 = 

VAR tbl=ADDCOLUMNS(SUMMARIZE('Table','Table'[Account],"last",CALCULATE(max('Table'[Last_process_time]),ALLEXCEPT('Table','Table'[Account])),"last2",maxx(FILTER('Table','Table'[Account]=EARLIER('Table'[Account])&&'Table'[Last_process_time]<CALCULATE(max('Table'[Last_process_time]),ALLEXCEPT('Table','Table'[Account]))),'Table'[Last_process_time])),"isoldrecord",maxx(FILTER('Table','Table'[Account]=EARLIER('Table'[Account])&&'Table'[Last_process_time]=[last]),'Table'[Credit_rating]),"islastproday",maxx(FILTER('Table','Table'[Account]=EARLIER('Table'[Account])&&'Table'[Last_process_time]=[last2]),'Table'[Credit_rating]))
RETURN SUMMARIZE(tbl,'Table'[Account],[isoldrecord],[islastproday])

    please see the attachment below.