Forum Discussion
Generate New table or calculated columns based on column and rows
I am very new to PowerBi and I am learning how to do the things I want by finding answers to similar issues raised in the forums and on blogs/videos.
I need to work out a period (start date and end date – in calculated columns) based on data from different rows and columns. I can do what I am after in nested if statements in Excel but I am struggling to work out how to do the same in PowerBI.
Scenario:
The data is for children whereby regular meetings occur. I need to identify a period (Safer Me CIN) based on an outcome of those meetings and the dates of those meetings.
Data structure:
Table - The meeting details come from answers filled in forms brought into PowerBi in ‘tbl_007SaferMeCIN – Form Details’. This table is linked (many - one) to the Person table ‘tbl_004_PersonView’
Relationship - tbl_004_PersonView[Eclipse ID] = tbl_007SaferMeCIN – Form Details[PERid]
Column Names = [PERid], [CALC_001 Date of Meeting], [CIN Meeting Number], [CALC_002 Safer Me CIN], [CIN Meeting Number]
Desired 2 Columns: [Start Date], [End Date]
Note – 3 of these columns are calculated columns – 2 as required to get the data from the source database and 1 to rank the meetings.
Logic:
Start Date = the date of the first meeting (CALC_001 Date of meeting) where the outcome (CALC-002 Safer Me CIN) = “Yes”. The child remains “Safer Me CIN” when the next meeting outcome remains “Yes”.
End Date = the date of the meeting minus 1 where the outcome is “No”.
Where the outcome of the meeting is “No” the child is not classed as in a period of Safer Me CIN (blank()).
Desired Outcome:
Originally I was wanting just 2 added calculated columns on the same table as the meeting data but thinking more logically, I would really like this data to generate a new table that can be linked back to the individual child (PersonView table).
New Table Desired Effect
| PER ID | Start Date | End Date |
| 1001 | 19/08/2020 | 17/09/2020 |
| 1002 | 07/12/2020 | 21/04/2021 |
| 1004 | 01/10/2020 | 21/10/2020 |
| 1004 | 16/11/2020 | 05/01/2021 |
| 1005 | 26/05/2021 | Blank() |
| 1006 | 29/07/2020 | Blank() |
Desired Effect if new calculated columns in original table
| PER ID | CALC_001 Date of Meeting | CIN Meeting Number | CALC_002 Safer Me CIN | Start Date | End Date |
| 1001 | 19/08/2020 | 1 | Yes | 19/08/2020 | 17/09/2020 |
| 1001 | 18/09/2020 | 2 | No | Blank() | Blank() |
| 1001 | 28/10/2020 | 3 | No | Blank() | Blank() |
| 1002 | 07/12/2020 | 1 | Yes | 07/12/2020 | 21/04/2021 |
| 1002 | 13/03/2021 | 2 | Yes | 07/12/2020 | 21/04/2021 |
| 1002 | 22/04/2021 | 3 | No | Blank() | Blank() |
| 1003 | 02/09/2020 | 1 | No | Blank() | Blank() |
| 1004 | 01/10/2020 | 1 | Yes | 01/10/2020 | 21/10/2020 |
| 1004 | 22/10/2020 | 2 | No | Blank() | Blank() |
| 1004 | 16/11/2020 | 3 | Yes | 16/11/2020 | 05/01/2021 |
| 1004 | 06/01/2021 | 4 | No | Blank() | Blank() |
| 1005 | 27/04/2021 | 1 | No | Blank() | Blank() |
| 1005 | 26/05/2021 | 2 | Yes | 26/05/2021 | Blank() |
| 1005 | 16/06/2021 | 3 | Yes | 26/05/2021 | Blank() |
| 1006 | 29/07/2020 | 1 | Yes | 29/07/2020 | Blank() |
| 1006 | 26/08/2020 | 2 | Yes | 29/07/2020 | Blank() |
| 1006 | 22/09/2020 | 3 | Yes | 29/07/2020 | Blank() |
| 1006 | 09/03/2021 | 4 | Yes | 29/07/2020 | Blank() |
Formula used in Excel:
Start Date - =IF(AND(A3<>A2,D3="Yes"),B3,IF(AND(A3=A2,D2="No", D3="Yes"), B3,IF(AND(A3=A2, D2="Yes", D3="Yes"),H2,IF(D3="No","","Check"))))
End Date - =IF(AND(A3=A4, D3="Yes",D4="No"),(B4)-1,IF(AND(A3<> A4, D3="Yes"),"",IF(AND(A3=A4, D3="Yes", D4="Yes"),I4,IF(D3="No","","Check"))))
Note – “check” only there to spot error in formula.
This solution uses both calculated columns and a calculated table.
1. Create calculated column [Start Date]:
Start Date = VAR vPerID = 'tbl_007SaferMeCIN – Form Details'[PER ID] VAR vMeetingDate = 'tbl_007SaferMeCIN – Form Details'[CALC_001 Date of Meeting] VAR vFirstMeetingDateOutcomeYes = CALCULATE ( MIN ( 'tbl_007SaferMeCIN – Form Details'[CALC_001 Date of Meeting] ), ALLEXCEPT ( 'tbl_007SaferMeCIN – Form Details', 'tbl_007SaferMeCIN – Form Details'[PER ID] ), 'tbl_007SaferMeCIN – Form Details'[CALC_002 Safer Me CIN] = "Yes" ) VAR vPrevMeetingDate = CALCULATE ( MAX ( 'tbl_007SaferMeCIN – Form Details'[CALC_001 Date of Meeting] ), ALLEXCEPT ( 'tbl_007SaferMeCIN – Form Details', 'tbl_007SaferMeCIN – Form Details'[PER ID] ), 'tbl_007SaferMeCIN – Form Details'[CALC_001 Date of Meeting] < vMeetingDate ) VAR vPrevMeetingDateOutcome = CALCULATE ( MAX ( 'tbl_007SaferMeCIN – Form Details'[CALC_002 Safer Me CIN] ), ALLEXCEPT ( 'tbl_007SaferMeCIN – Form Details', 'tbl_007SaferMeCIN – Form Details'[PER ID] ), 'tbl_007SaferMeCIN – Form Details'[CALC_001 Date of Meeting] = vPrevMeetingDate ) VAR vCountOutcomeNo = COUNTROWS ( FILTER ( 'tbl_007SaferMeCIN – Form Details', 'tbl_007SaferMeCIN – Form Details'[PER ID] = vPerID && 'tbl_007SaferMeCIN – Form Details'[CALC_002 Safer Me CIN] = "No" && 'tbl_007SaferMeCIN – Form Details'[CALC_001 Date of Meeting] > vFirstMeetingDateOutcomeYes && 'tbl_007SaferMeCIN – Form Details'[CALC_001 Date of Meeting] < vMeetingDate ) ) VAR vResult = SWITCH ( TRUE (), 'tbl_007SaferMeCIN – Form Details'[CALC_002 Safer Me CIN] = "No", BLANK (), ISBLANK ( vCountOutcomeNo ), vFirstMeetingDateOutcomeYes, vPrevMeetingDateOutcome = "No", vMeetingDate ) RETURN vResult2. Create calculated column [End Date]:
End Date = VAR vMeetingDate = 'tbl_007SaferMeCIN – Form Details'[CALC_001 Date of Meeting] VAR vNextMeetingDateOutcomeNo = CALCULATE ( MIN ( 'tbl_007SaferMeCIN – Form Details'[CALC_001 Date of Meeting] ), ALLEXCEPT ( 'tbl_007SaferMeCIN – Form Details', 'tbl_007SaferMeCIN – Form Details'[PER ID] ), 'tbl_007SaferMeCIN – Form Details'[CALC_002 Safer Me CIN] = "No", 'tbl_007SaferMeCIN – Form Details'[CALC_001 Date of Meeting] > vMeetingDate ) VAR vResult = SWITCH ( TRUE (), 'tbl_007SaferMeCIN – Form Details'[CALC_002 Safer Me CIN] = "No" || ISBLANK ( vNextMeetingDateOutcomeNo ), BLANK (), 'tbl_007SaferMeCIN – Form Details'[CALC_002 Safer Me CIN] = "Yes", vNextMeetingDateOutcomeNo - 1 ) RETURN vResult3. Create calculated table:
New Table = CALCULATETABLE ( SUMMARIZE ( 'tbl_007SaferMeCIN – Form Details', 'tbl_007SaferMeCIN – Form Details'[PER ID], 'tbl_007SaferMeCIN – Form Details'[Start Date], 'tbl_007SaferMeCIN – Form Details'[End Date] ), NOT ( ISBLANK ( 'tbl_007SaferMeCIN – Form Details'[Start Date] ) ) )
2 Replies
- DataInsights
Super User
This solution uses both calculated columns and a calculated table.
1. Create calculated column [Start Date]:
Start Date = VAR vPerID = 'tbl_007SaferMeCIN – Form Details'[PER ID] VAR vMeetingDate = 'tbl_007SaferMeCIN – Form Details'[CALC_001 Date of Meeting] VAR vFirstMeetingDateOutcomeYes = CALCULATE ( MIN ( 'tbl_007SaferMeCIN – Form Details'[CALC_001 Date of Meeting] ), ALLEXCEPT ( 'tbl_007SaferMeCIN – Form Details', 'tbl_007SaferMeCIN – Form Details'[PER ID] ), 'tbl_007SaferMeCIN – Form Details'[CALC_002 Safer Me CIN] = "Yes" ) VAR vPrevMeetingDate = CALCULATE ( MAX ( 'tbl_007SaferMeCIN – Form Details'[CALC_001 Date of Meeting] ), ALLEXCEPT ( 'tbl_007SaferMeCIN – Form Details', 'tbl_007SaferMeCIN – Form Details'[PER ID] ), 'tbl_007SaferMeCIN – Form Details'[CALC_001 Date of Meeting] < vMeetingDate ) VAR vPrevMeetingDateOutcome = CALCULATE ( MAX ( 'tbl_007SaferMeCIN – Form Details'[CALC_002 Safer Me CIN] ), ALLEXCEPT ( 'tbl_007SaferMeCIN – Form Details', 'tbl_007SaferMeCIN – Form Details'[PER ID] ), 'tbl_007SaferMeCIN – Form Details'[CALC_001 Date of Meeting] = vPrevMeetingDate ) VAR vCountOutcomeNo = COUNTROWS ( FILTER ( 'tbl_007SaferMeCIN – Form Details', 'tbl_007SaferMeCIN – Form Details'[PER ID] = vPerID && 'tbl_007SaferMeCIN – Form Details'[CALC_002 Safer Me CIN] = "No" && 'tbl_007SaferMeCIN – Form Details'[CALC_001 Date of Meeting] > vFirstMeetingDateOutcomeYes && 'tbl_007SaferMeCIN – Form Details'[CALC_001 Date of Meeting] < vMeetingDate ) ) VAR vResult = SWITCH ( TRUE (), 'tbl_007SaferMeCIN – Form Details'[CALC_002 Safer Me CIN] = "No", BLANK (), ISBLANK ( vCountOutcomeNo ), vFirstMeetingDateOutcomeYes, vPrevMeetingDateOutcome = "No", vMeetingDate ) RETURN vResult2. Create calculated column [End Date]:
End Date = VAR vMeetingDate = 'tbl_007SaferMeCIN – Form Details'[CALC_001 Date of Meeting] VAR vNextMeetingDateOutcomeNo = CALCULATE ( MIN ( 'tbl_007SaferMeCIN – Form Details'[CALC_001 Date of Meeting] ), ALLEXCEPT ( 'tbl_007SaferMeCIN – Form Details', 'tbl_007SaferMeCIN – Form Details'[PER ID] ), 'tbl_007SaferMeCIN – Form Details'[CALC_002 Safer Me CIN] = "No", 'tbl_007SaferMeCIN – Form Details'[CALC_001 Date of Meeting] > vMeetingDate ) VAR vResult = SWITCH ( TRUE (), 'tbl_007SaferMeCIN – Form Details'[CALC_002 Safer Me CIN] = "No" || ISBLANK ( vNextMeetingDateOutcomeNo ), BLANK (), 'tbl_007SaferMeCIN – Form Details'[CALC_002 Safer Me CIN] = "Yes", vNextMeetingDateOutcomeNo - 1 ) RETURN vResult3. Create calculated table:
New Table = CALCULATETABLE ( SUMMARIZE ( 'tbl_007SaferMeCIN – Form Details', 'tbl_007SaferMeCIN – Form Details'[PER ID], 'tbl_007SaferMeCIN – Form Details'[Start Date], 'tbl_007SaferMeCIN – Form Details'[End Date] ), NOT ( ISBLANK ( 'tbl_007SaferMeCIN – Form Details'[Start Date] ) ) )Thank you so much for this. It seems to be working beautifully. Now I will study the code to learn how it works so I can write this style of code in the future.😁
