Summarizing credit score results by date order

[Nels921]

I have a table that shows the credit scores for applicants everytime they submit an application.  This is a sample of the data and I'm only using this table to develop my report. Everytime an applicant does a credit check, a new row is inserted in the table with their score, result and the date of the credit check. 

Applicant	Credit Score	Result	Date
Company xyz	1320	Pass	2022-10-02
Company xyz	1315	Fail	2021-03-24
Company xyz	1341	Pass	2021-10-02
Company abc	1375	Pass	2021-11-16
Company123	1168	Fail	2021-02-26
Company123	1367	Pass	2022-01-27

I'm trying to create a report that summarises the credit scores by company in one row in the order that they were done (e.g 1st credit score should be the one that was the earliest date) so that we can see if there are any trends.  This is an example of how the table should  ideally be presented:

Applicant	1st Credit Score	1st Result	2nd Credit score	2nd Result	3rd Credit score	3rd result	4th----
Company xyz	1315	Fail	1341	Pass	1320	Pass
Company abc	1375	Pass
Company123	1168	Fail	1367	Pass

I'm faily new to powrbi and have been stuck on this for a few days and I'd appreciate your advice. 

[Nels921]

Thank you for your response. I tried the formula and it gave me the correct visual: 

However, I'm finding it challenging to filter and query the results.  As a next step in the analysis I need create different groups for the applicants (eg. those who failed the first credit check but passed on the 2nd one; or those that passed on the first but failed on the second).  What I had in mind initially was to just use different filter combinations to create the groups. But I'm not sure how to do that because the 1st, 2nd, 3rd results are not stored in their own separate columns. I was able to find the ones that passed on all credit checks and the ones who failed in all, but I haven't been able to do a combination of the two. 

I also tried exporing the visual into excel to see if I could create a pivot table but this was how that data was exported to csv. 

[Anonymous · Accepted solution]

Hi @Nels921 ,

I create a simple data for testing, hope it can help you:

this is my data:

according to your description, I think we can create a measure to get two groups( those who failed the first credit check but passed on the 2nd one; or those that passed on the first but failed on the second).

Measure = IF(SELECTEDVALUE('Table'[1 check])="pass"&&SELECTEDVALUE('Table'[2 check])="fail",1,IF(SELECTEDVALUE('Table'[1 check])="fail"&&SELECTEDVALUE('Table'[2 check])="pass",0))

Then we can through measure to filter:

Best regards,

Community Support Team Selina zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anonymous]

Hi,

Just add a clumn on your table with this code :

Position =

var valcherhc='Table'[Date]

return

concatenate(calculate(RANK.EQ(valcherhc,'Table'[Date],ASC),ALLEXCEPT('Table','Table'[Applicant]))," tour")

and it will give you this result