Query to report date and results based on conditions

[elleeyl]

Hi,

I am very new to Power BI and have inherited this dashboard. There is a recent request to change a report condition and I'm stuck. Would appreciate any help, please. Thank you.

In this dataset, I have 3 test papers. Each NRIC is unique - represents a person taking the tests. For each paper, if the person is absent from the test, data columns F, G, L - O will be blank. When a person is absent from a paper, the logic is to take the last valid (non-blank) result and populate the report. 

 

E.g. Person C: Present for Papers 1-3 the very first time, hence populate Test Start Date Time and Percentage of Correct Answers as shown .

Person A: Absent for second attempt of Paper 1, hence populate Test Start Date Time and Percentage of Correct Answers of first attempt, as shown in red.

Person B: Absent for first attempt of Paper 1. Nothing to fall back on, hence populate Test Start Date Time of first attempt as shown in red. Percentage of Correct Answers to leave as blank.

Person 😧 Absent for second and thirds attempts of Paper 1. hence populate Test Start Date Time and Percentage of Correct Answers of first attempt, as shown in red.

Person E: Present for both attempts of Paper 1, hence populate Test Start Date Time and Percentage of Correct Answers of last attempt, as shown in red.

The current Power BI dashboard logic will always pick up the last attempt, so if the person is absent, it will be reflected as blank. 

 I have attached both data source and PBIX files. Thank you very much.

 https://drive.google.com/file/d/1xCwUcA0c9giiWpJ4429CubyrEpxfy55Y/view?usp=sharing

[CheenuSing · Accepted solution]

Hi @elleeyl

I did the following to youyr sheet1 table power query.

After the changedtype 5 step of the power query, added the following steps

1. Added Index - from 1

2. Added Index - from 0

3. Did a self Merge

    #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index"},#"Added Index1",{"Index.1"},"Added Index1",JoinKind.LeftOuter),

The objective of this is to find the values of the NRIC / Test Topic from the NEXT record of the table.

4. After expanding the merged table and removing unwanted columns did

     #"Grouped Rows" = Table.Group(#"Removed Columns2", {"NRIC", "Test Topic"}, {{"Rows", each Table.RowCount(_), type number}, {"AllROws", each _, type table}}),

 The objective is to find the count of rows for each combination of NRIC / Test Topic

5. After expanding the table at step 4 and removing and renaming columns did the final step of adding a conditional column

 #"Added Conditional Column" = Table.AddColumn(#"Renamed Columns1", "ShowRed", each if [Rows] = 1 then 1
                                                                                  else if ( [Test Topic] & [NRIC] = [AllROws.Added Index1.Test Topic] & [AllROws.Added Index1.NRIC]
                                                                                                and ([AllROws.PassedFailed] <> null
                                                                                                and [AllROws.Added.PassedFailed] = null) )
                                                                                       then 1
                                                                                  else if (   
                          [AllROws.Added Index1.Test Topic] = null and [AllROws.Added Index1.Test Session Start Date Time] = null and
                          [AllROws.Added Index1.Name] = null and [AllROws.Added Index1.NRIC] & [AllROws.Added.PassedFailed] = null ) then 1

                                                             else   0),

The logic is

a )If Rows =1 then it is the only record of the NRIC/Test Topic and therefore show the row.

b) if ( [Test Topic] & [NRIC] = [AllROws.Added Index1.Test Topic] & [AllROws.Added Index1.NRIC]
                                                                                                and ([AllROws.PassedFailed] <> null
                                                                                                and [AllROws.Added.PassedFailed] = null) )
                                                                                       then 1 

  If NRIC / Test Topic of the successive records are same and the values of the current records PassedFailed is not null and the next record PassedFailed is blank then show the current row.

c) if (   
                          [AllROws.Added Index1.Test Topic] = null and [AllROws.Added Index1.Test Session Start Date Time] = null and
                          [AllROws.Added Index1.Name] = null and [AllROws.Added Index1.NRIC] & [AllROws.Added.PassedFailed] = null ) then 1

If the values from the next row is all blank then show the current row.

d) if all above fails then don't show the current record.

Set the column ShowRed as type  WholeNumber.

Created a table and set the SessionStart Date column as condition formatting set the colour as Red if ShowRed value is 1 and Black if the show red colour is 0.

Sample output

                                     I am also attaching the link to google drive of the pbix file.

https://drive.google.com/open?id=1M4kvl5GKKJaEg88q1chrN_mv9aGXZ7El

One assumption that I have made is each NRIC/Test Topic will have a maximum of only record with not taking the exam.

If this works for you please accept this as a solution and also give KUDOS.

Cheers

CheenuSing

Singapore

[elleeyl]

@CheenuSing Thank you for your prompt response!