cancel
Showing results for
Did you mean:
Frequent Visitor

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

1 ACCEPTED SOLUTION
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
vResult``````

2. 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
vResult``````

3. 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] ) )
)``````

Proud to be a Super User!

2 REPLIES 2
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
vResult``````

2. 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
vResult``````

3. 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] ) )
)``````

Proud to be a Super User!

Frequent Visitor

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

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors