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 de...
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.

ryan_mayu's avatar
ryan_mayu
Icon for Super User rankSuper 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.