Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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 🙂
Solved! Go to Solution.
@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.
Proud to be a Super User!
@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.
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |