cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lyndaGough
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 IDStart DateEnd Date
100119/08/202017/09/2020
100207/12/202021/04/2021
100401/10/202021/10/2020
100416/11/202005/01/2021
100526/05/2021Blank()
100629/07/2020Blank()

 

 

Desired Effect if new calculated columns in original table

PER IDCALC_001 Date of MeetingCIN Meeting NumberCALC_002 Safer Me CINStart DateEnd Date
100119/08/20201Yes19/08/202017/09/2020
100118/09/20202NoBlank()Blank()
100128/10/20203NoBlank()Blank()
100207/12/20201Yes07/12/202021/04/2021
100213/03/20212Yes07/12/202021/04/2021
100222/04/20213NoBlank()Blank()
100302/09/20201NoBlank()Blank()
100401/10/20201Yes01/10/202021/10/2020
100422/10/20202NoBlank()Blank()
100416/11/20203Yes16/11/202005/01/2021
100406/01/20214NoBlank()Blank()
100527/04/20211NoBlank()Blank()
100526/05/20212Yes26/05/2021Blank()
100516/06/20213Yes26/05/2021Blank()
100629/07/20201Yes29/07/2020Blank()
100626/08/20202Yes29/07/2020Blank()
100622/09/20203Yes29/07/2020Blank()
100609/03/20214Yes29/07/2020Blank()

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
DataInsights
Super User
Super User

@lyndaGough,

 

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] ) )
)

 

DataInsights_0-1627867866210.png

View solution in original post

2 REPLIES 2
DataInsights
Super User
Super User

@lyndaGough,

 

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] ) )
)

 

DataInsights_0-1627867866210.png

View solution in original post

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

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.